SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMCOMP_RETE_Q01]
/*-------------------------------------------------------*/          
/*---Empresa               OFISIS S.A.                 --*/          
/*---Cliente               CPPQ S.A.                   --*/          
/*---Sistema               Tesoreria                   --*/          
/*---Modulo                Empresa                     --*/          
/*---Programa              Comprobantes de Retencion   --*/          
/*---Script                SP_TMCOMP_RETE_Q01.sql      --*/          
/*---Nombre SP             SP_TMCOMP_RETE_Q01          --*/          
/*---Desarrollado por      Denis Villacres Puerta      --*/          
/*---Fecha Creaci«n        15/06/2002                  --*/          
/*---Base Datos            SQL                   --*/          
/*---Versi«n               2000                        --*/          
/*---Invoca a SP                                       --*/          
/*-------------------------------------------------------*/          
          
/*--------------------------------------------------------*/          
/*---Modificado 1 por     :  D.CH.   --*/          
/*---Fecha Modificaci«n   :  12/12/2002  --*/           
/*---Detalle Modificaci«n :  Se agreg½ cancelaciones --*/          
/*---    por rendici½n de gastos --*/          
/*--------------------------------------------------------*/          
          
/*--------------------------------------------------------*/          
/*--- Drop Proc SP_TMCOMP_RETE_Q01                      --*/          
/*--- Grant all on SP_TMCOMP_RETE_Q01 to public         --*/           
/*--- SP_TMCOMP_RETE_Q01 '01', '2005','05', NULL, 'S', 'C'   
	CREATE TABLE #TWREGI_RETE_Q01(
	CO_PROV		Varchar(20) Null,
	NO_PROV		Varchar(100) Null,
	FE_EMIS_DOCU		Datetime Null,
	FE_PARA_REPO		Datetime Null,
	CO_MONE_DOCU		Varchar(3) Null,
	FA_TICA_DOCU		Numeric(16,4) Default 0,
	IM_DOCU_SOLE		Numeric(16,4) Default 0,
	IM_DOCU_DOLA		Numeric(16,4) Default 0,
	NU_SERI_DOCU		Varchar(6) Null,
	NU_CORR_DOCU		Varchar(20) Null,
	NU_DOCU_REFE		Varchar(20) Null,
	NU_CORR_PROV		Varchar(20) Null,
	FE_PAGO_BACA		Datetime Null,
	CO_SUNA_DOCU		Varchar(20) Null,
	CO_BACA_PAGO		Varchar(80) Null,
	CO_MONE_PAGO		Varchar(3) Null,
	NU_SERI_RETE		Varchar(6) Null,
	NU_CORR_RETE		Varchar(20) Null,
	FA_TICA_PAGO		Numeric(16,4) Default 0,
	IM_PAGO_SOLE		Numeric(16,4) Default 0,
	IM_PAGO_DOLA		Numeric(16,4) Default 0,
	IM_RETE_SOLE		Numeric(16,4) Default 0,
	IM_RETE_DOLA		Numeric(16,4) Default 0,
	IM_TORE_SOLE		Numeric(16,4) Default 0,
	IM_TORE_DOLA		Numeric(16,4) Default 0,
	TI_OPER			Varchar(50) Null,
	FE_OPER		Datetime Null,
	NU_ORDE	 	Numeric(16,4) Default 0,
	IM_DIFE_DEBE		Numeric(16,4) Default 0,
	IM_DIFE_HABER		Numeric(16,4) Default 0,
	FA_TICA_OPER		Numeric(16,4) Default 0,
	NU_CHEQ		Varchar(20) Null)

	CREATE TABLE #TWREGI_RETE_Q02(
	CO_PROV		Varchar(20) Null,
	NO_PROV		Varchar(100) Null,
	FE_EMIS_DOCU		Datetime Null,
	CO_MONE_DOCU		Varchar(3) Null,
	FA_TICA_DOCU		Numeric(16,4) Default 0,
	IM_DOCU_SOLE		Numeric(16,4) Default 0,
	IM_DOCU_DOLA		Numeric(16,4) Default 0,
	NU_SERI_DOCU		Varchar(6) Null,
	NU_CORR_DOCU		Varchar(20) Null,
	NU_DOCU_REFE		Varchar(20) Null,
	NU_CORR_PROV		Varchar(20) Null,
	FE_PAGO_BACA		Datetime Null,
	CO_SUNA_DOCU		Varchar(20) Null,
	CO_BACA_PAGO		Varchar(80) Null,
	CO_MONE_PAGO		Varchar(3) Null,
	NU_SERI_RETE		Varchar(6) Null,
	NU_CORR_RETE		Varchar(20) Null,
	FA_TICA_PAGO		Numeric(16,4) Default 0,
	IM_PAGO_SOLE		Numeric(16,4) Default 0,
	IM_PAGO_DOLA		Numeric(16,4) Default 0,
	IM_RETE_SOLE		Numeric(16,4) Default 0,
	IM_RETE_DOLA		Numeric(16,4) Default 0,
	IM_TORE_SOLE		Numeric(16,4) Default 0,
	IM_TORE_DOLA		Numeric(16,4) Default 0,
	TI_OPER			Varchar(50) Null,
	FE_OPER		Datetime Null,
	NU_ORDE	 	Numeric(16,4) Default 0,
	IM_DIFE_DEBE		Numeric(16,4) Default 0,
	IM_DIFE_HABER		Numeric(16,4) Default 0,
	FA_TICA_OPER		Numeric(16,4) Default 0,
	NU_CHEQ		Varchar(20) Null)
      --*/          
/*--------------------------------------------------------*/          
          
          
@ISCO_EMPR   TD_VC_002,           
@ISNU_ANNO   TD_VC_004,           
@ISNU_MESE   TD_VC_002,           
@ISCO_PROV_REPO  TD_VC_020,          
@ISST_RETE TD_VC_001,      
@ISST_ORDE TD_VC_001
	As
DECLARE           
@VDFE_INIC  TD_DT_001,  
@VDFE_EMIS  TD_DT_001,  
@VDFE_FINA  TD_DT_001,  
@VNNU_DIFI  TD_IN_001,  
@VSNU_DIFI  TD_VC_002  
  
-- OPERACIONES          
-- 1   => Reg. Compras          
-- 2   => Canje          
-- 2.5 => Dif Camb Canje          
-- 3   => Letras          
-- 4   => Pago por Bancos / Caja          
-- 5   => Dif Camb Pago          
-- 6   => Retencion          
          
          
-- RANGO DE FECHAS          
          
SELECT @VDFE_INIC = Convert(Datetime, Replicate('0', Abs(Len(Ltrim(Str(@ISNU_MESE)))-2)) + Ltrim(Str(@ISNU_MESE)) + '/'+ '01/' + Ltrim(Str(@ISNU_ANNO)))          
SELECT @VDFE_EMIS = Convert(Varchar(10), Convert(Datetime, '01/06/2002'), 103)          
          
If @ISNU_MESE = '01' Or @ISNU_MESE = '03' Or @ISNU_MESE = '05' Or @ISNU_MESE = '07'           
   Or @ISNU_MESE = '08' Or @ISNU_MESE = '10' Or @ISNU_MESE = '12'           
 Select @VNNU_DIFI = 31          
Else          
    Begin          
 If @ISNU_MESE = '02'           
    Begin          
       If Convert(Int, @ISNU_ANNO)%4 = 0          
         Select @VNNU_DIFI = 29          
  Else          
    Select @VNNU_DIFI = 28          
    End          
 Else          
    Select @VNNU_DIFI = 30          
    End          
          
Select @VSNU_DIFI = Convert(Varchar(2), @VNNU_DIFI )          
    
Select @VDFE_FINA = DATEADD(Day, -1, DATEADD(Month, 1, @VDFE_INIC) ) 
          
/* Borro el contenido de ambas Tablas Temporales*/          
--DELETE #TWREGI_RETE_Q01          
--DELETE #TWREGI_RETE_Q02          
          
         
/* DOCUMENTOS PENDIENTES DE PAGO*/           
          
-- DE LA TMDOCU_PROV          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, FE_PARA_REPO, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, FA_TICA_OPER,           
 CO_BACA_PAGO, CO_MONE_PAGO,           
 TI_OPER, NU_ORDE)          
          
Select  T1.CO_PROV, T3.NO_CORT_PROV, T1.FE_REGI_COMP, FE_EMIS,           
 T1.CO_MONE, T1.FA_TIPO_CAMB,           
          
 CASE WHEN T2.CO_SUNA != '07' THEN          
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA           
 ELSE 0 END 'DOCU_SOLE',           
          
 CASE WHEN T2.CO_SUNA != '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA           
 ELSE 0 END 'DOCU_DOLA',           
          
          
 CASE WHEN T2.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA           
 ELSE 0 END 'PAGO_SOLE',           
          
 CASE WHEN T2.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA           
 ELSE 0 END 'PAGO_DOLA',           
          
 SubString(T1.NU_DOCU_PROV, 1 ,Patindex('%-%', T1.NU_DOCU_PROV) - 1),          
 SubString(T1.NU_DOCU_PROV, Patindex('%-%', T1.NU_DOCU_PROV) + 1, Len(T1.NU_DOCU_PROV)),           
 T1.NU_CORR_COMP,           
 T2.CO_SUNA, T1.FA_TIPO_CAMB,           
 T1.NU_CORR_COMP,           
          
 CASE WHEN T2.CO_SUNA = '07' THEN          
 T1.CO_MONE          
 ELSE NULL END,           
          
 'Reg. Compras', 1          
From   TMDOCU_PROV T1, TTDOCU_CNTB T2, TMPROV T3          
Where  T1.CO_EMPR = @ISCO_EMPR          
   And T1.CO_ESTA_DOCU != 'ANU'          
   And T1.CO_TIPO_DOCU = T2.TI_DOCU          
   And T2.ST_RETE = 'S'          
   And T1.CO_EMPR = T3.CO_EMPR          
   And T1.CO_PROV = T3.CO_PROV          
 And T3.ST_RETE = 'N'          
 And T3.ST_BCON = 'N'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 And T1.FE_EMIS >= @VDFE_EMIS          
 And T1.FE_EMIS <= @VDFE_FINA           
          
-------------------------------------          
 And T1.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T1.CO_IMP1 LIKE 'IG%' OR T1.CO_IMP2 LIKE 'IG%' OR T1.CO_IMP3 LIKE 'IG%' ) AND ( T1.IM_IMP1 > 0 OR T1.IM_IMP2 > 0 OR T1.IM_IMP3 > 0) )          
          
 And Not Exists(Select   T5.CO_EMPR           
         From     TCCOMP_RETE T5, TDCOMP_RETE T6          
         Where    T5.CO_EMPR = @ISCO_EMPR          
       And T5.CO_EMPR = T6.CO_EMPR          
       And T5.TI_SITU = 'ACT'          
       And T5.NU_COMP_RETE = T6.NU_COMP_RETE          
       And T1.CO_PROV = T5.CO_PROV          
       And T1.CO_TIPO_DOCU = T6.TI_DOCU           
       And T1.NU_DOCU_PROV = T6.NU_DOCU)          
          
 And Not Exists(Select   T9.CO_EMPR           
         From     TCCANJ_PROV T8, TDCANJ_PROV T9          
         Where    T8.CO_EMPR = @ISCO_EMPR          
    And T8.CO_PROV = T1.CO_PROV           
    And T8.TI_SITU != 'ANU'          
    --And T8.FE_CANJ >= @VDFE_EMIS          
    --And T8.FE_CANJ <= @VDFE_FINA          
    AND           
    (          
    ( YEAR(T8.FE_CANJ) != YEAR(@VDFE_FINA) )          
    OR           
    ( YEAR(T8.FE_CANJ) = YEAR(@VDFE_FINA) AND MONTH(T8.FE_CANJ) < MONTH(@VDFE_FINA) )          
    )          
          
    --And MONTH(T8.FE_CANJ) != MONTH(@VDFE_FINA)          
          
       And T8.CO_EMPR = T9.CO_EMPR          
       And T8.CO_PROV = T9.CO_PROV          
       And T8.NU_CANJ = T9.NU_CANJ          
    And T1.CO_PROV = T9.CO_PROV           
       And T1.CO_TIPO_DOCU = T9.CO_TIPO_DOCU          
       And T1.NU_DOCU_PROV = T9.NU_DOCU_PROV)          
          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, FE_PARA_REPO, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, FA_TICA_OPER,           
 CO_BACA_PAGO, CO_MONE_PAGO,           
 TI_OPER, NU_ORDE)          
          
Select  T1.CO_PROV, T3.NO_CORT_PROV, T1.FE_REGI_COMP, FE_EMIS,           
 T1.CO_MONE, T1.FA_TIPO_CAMB,           
          
 CASE WHEN T2.CO_SUNA != '07' THEN          
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA           
 ELSE 0 END,           
          
 CASE WHEN T2.CO_SUNA != '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA           
 ELSE 0 END,           
          
          
 CASE WHEN T2.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA           
 ELSE 0 END,           
      
CASE WHEN T2.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA           
 ELSE 0 END,          
          
 SubString(T1.NU_DOCU_PROV, 1 ,Patindex('%-%', T1.NU_DOCU_PROV) - 1),          
 SubString(T1.NU_DOCU_PROV, Patindex('%-%', T1.NU_DOCU_PROV) + 1, Len(T1.NU_DOCU_PROV)),           
 T1.NU_CORR_COMP,           
 T2.CO_SUNA, T1.FA_TIPO_CAMB,           
 T1.NU_CORR_COMP,           
          
 CASE WHEN T2.CO_SUNA = '07' THEN        
 T1.CO_MONE          
 ELSE NULL END,          
          
 'Reg. Compras', 1          
From   TMDOCU_PROV T1, TTDOCU_CNTB T2, TMPROV T3          
Where  T1.CO_EMPR = @ISCO_EMPR          
   And T1.CO_ESTA_DOCU != 'ANU'          
   And T1.CO_TIPO_DOCU = T2.TI_DOCU          
   And T2.ST_RETE = 'S'          
   And T1.CO_EMPR = T3.CO_EMPR          
   And T1.CO_PROV = T3.CO_PROV          
 And T3.ST_RETE = 'N'          
 And T3.ST_BCON = 'N'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 And T1.FE_EMIS >= @VDFE_EMIS          
 And T1.FE_EMIS <= @VDFE_FINA          
-------------------------------------          
 And T1.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 And Exists(Select  T5.CO_EMPR           
     From    TCCOMP_RETE T5, TDCOMP_RETE T6          
     Where   T5.CO_EMPR = @ISCO_EMPR          
      And T5.CO_EMPR = T6.CO_EMPR          
      And T5.TI_SITU = 'ACT'          
      And T5.NU_COMP_RETE = T6.NU_COMP_RETE          
      And T1.CO_PROV = T5.CO_PROV          
      And T1.CO_TIPO_DOCU = T6.TI_DOCU           
      And T1.NU_DOCU_PROV = T6.NU_DOCU)          
          
 And Not Exists(Select   T9.CO_EMPR           
         From     TCCANJ_PROV T8, TDCANJ_PROV T9          
         Where    T8.CO_EMPR = @ISCO_EMPR          
    And T8.CO_PROV = T1.CO_PROV           
    And T8.TI_SITU != 'ANU'          
    --And T8.FE_CANJ >= @VDFE_EMIS          
    --And T8.FE_CANJ <= @VDFE_FINA          
    AND           
    (          
    ( YEAR(T8.FE_CANJ) != YEAR(@VDFE_FINA) )          
    OR           
    ( YEAR(T8.FE_CANJ) = YEAR(@VDFE_FINA) AND MONTH(T8.FE_CANJ) < MONTH(@VDFE_FINA) )          
    )          
          
    --And MONTH(T8.FE_CANJ) != MONTH(@VDFE_FINA)          
       And T8.CO_EMPR = T9.CO_EMPR          
       And T8.CO_PROV = T9.CO_PROV          
       And T8.NU_CANJ = T9.NU_CANJ          
    And T1.CO_PROV = T9.CO_PROV           
       And T1.CO_TIPO_DOCU = T9.CO_TIPO_DOCU          
       And T1.NU_DOCU_PROV = T9.NU_DOCU_PROV)          
          
          
IF @ISST_RETE = 'S'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, FE_PARA_REPO, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, FA_TICA_OPER,           
 CO_BACA_PAGO, CO_MONE_PAGO,           
 TI_OPER, NU_ORDE)          
          
Select  T1.CO_PROV, T3.NO_CORT_PROV, T1.FE_REGI_COMP, FE_EMIS,           
 T1.CO_MONE, T1.FA_TIPO_CAMB,           
          
 CASE WHEN T2.CO_SUNA != '07' THEN          
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA           
 ELSE 0 END,           
          
 CASE WHEN T2.CO_SUNA != '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA           
 ELSE 0 END,           
          
          
 CASE WHEN T2.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA           
 ELSE 0 END,           
          
 CASE WHEN T2.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA           
 ELSE 0 END,          
          
 SubString(T1.NU_DOCU_PROV, 1 ,Patindex('%-%', T1.NU_DOCU_PROV) - 1),          
 SubString(T1.NU_DOCU_PROV, Patindex('%-%', T1.NU_DOCU_PROV) + 1, Len(T1.NU_DOCU_PROV)),           
 T1.NU_CORR_COMP,           
 T2.CO_SUNA, T1.FA_TIPO_CAMB,           
 T1.NU_CORR_COMP,           
          
 CASE WHEN T2.CO_SUNA = '07' THEN          
 T1.CO_MONE          
 ELSE NULL END,          
          
 'Reg. Compras', 1          
From   TMDOCU_PROV T1, TTDOCU_CNTB T2, TMPROV T3          
Where  T1.CO_EMPR = @ISCO_EMPR          
   And T1.CO_ESTA_DOCU != 'ANU'          
   And T1.CO_TIPO_DOCU = T2.TI_DOCU          
   And T2.ST_RETE = 'S'          
   And T1.CO_EMPR = T3.CO_EMPR          
   And T1.CO_PROV = T3.CO_PROV          
 And T3.ST_RETE = 'N'          
 And T3.ST_BCON = 'N'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 And T1.FE_EMIS >= @VDFE_EMIS          
 And T1.FE_EMIS <= @VDFE_FINA          
-------------------------------------          
 And T1.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 And Exists(Select  T5.CO_EMPR           
     From    TCCOMP_RETE T5, TDCOMP_RETE T6          
     Where   T5.CO_EMPR = @ISCO_EMPR          
      And T5.CO_EMPR = T6.CO_EMPR          
      --And T5.TI_SITU = 'ACT'          
      And T5.NU_COMP_RETE = T6.NU_COMP_RETE          
      And YEAR(T5.FE_COMP_RETE) = CONVERT(INT, @ISNU_ANNO)          
      And MONTH(T5.FE_COMP_RETE) = CONVERT(INT, @ISNU_MESE)          
      And T1.CO_PROV = T5.CO_PROV          
      And T1.CO_TIPO_DOCU = T6.TI_DOCU           
      And T1.NU_DOCU_PROV = T6.NU_DOCU)          
          
 And Not Exists(Select   T9.CO_EMPR           
         From     TCCANJ_PROV T8, TDCANJ_PROV T9          
         Where    T8.CO_EMPR = @ISCO_EMPR          
    And T8.CO_PROV = T1.CO_PROV           
    And T8.TI_SITU != 'ANU'          
    --And T8.FE_CANJ >= @VDFE_EMIS          
    --And T8.FE_CANJ <= @VDFE_FINA          
    AND           
    (          
    ( YEAR(T8.FE_CANJ) != YEAR(@VDFE_FINA) )        OR           
    ( YEAR(T8.FE_CANJ) = YEAR(@VDFE_FINA) AND MONTH(T8.FE_CANJ) < MONTH(@VDFE_FINA) )          
    )          
          
    --And MONTH(T8.FE_CANJ) != MONTH(@VDFE_FINA)          
       And T8.CO_EMPR = T9.CO_EMPR          
       And T8.CO_PROV = T9.CO_PROV          
       And T8.NU_CANJ = T9.NU_CANJ          
    And T1.CO_PROV = T9.CO_PROV           
       And T1.CO_TIPO_DOCU = T9.CO_TIPO_DOCU          
       And T1.NU_DOCU_PROV = T9.NU_DOCU_PROV)          
          
          
/* CANJES */          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_DOCU_REFE,           
 CO_BACA_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 CO_MONE_PAGO,           
 FA_TICA_OPER, TI_OPER, FE_OPER, NU_ORDE)          
          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T1.FE_CANJ, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 T5.CO_SUNA+' '+          
 SubString(T2.NU_DOCU_PROV, 1, Patindex('%-%', T2.NU_DOCU_PROV) - 1)+' '+          
 SubString(T2.NU_DOCU_PROV, Patindex('%-%', T2.NU_DOCU_PROV) + 1, Len(T2.NU_DOCU_PROV)),          
          
 T1.NU_CANJ,           
 T1.FE_CANJ,           
 T1.FA_TIPO_CAMB,           
          
 CASE WHEN T5.CO_SUNA != '07' THEN           
 PatIndex(T1.CO_MONE, 'SOL')*T2.IM_CANJ_MDOC           
 ELSE 0 END 'DOCU_SOLE',           
          
 CASE WHEN T5.CO_SUNA != '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T2.IM_CANJ_MDOC           
 ELSE 0 END 'DOCU_DOLA',           
          
 CASE WHEN T5.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'SOL')*T2.IM_CANJ_MDOC           
 ELSE 0 END 'PAGO_SOLE',           
          
 CASE WHEN T5.CO_SUNA = '07' THEN          
 PatIndex(T1.CO_MONE, 'DOL')*T2.IM_CANJ_MDOC           
 ELSE 0 END 'PAGO_DOLA',           
          
 --PatIndex(T1.CO_MONE, 'SOL')*T2.IM_CANJ_MDOC,           
 --PatIndex(T1.CO_MONE, 'DOL')*T2.IM_CANJ_MDOC,          
 T4.CO_MONE,           
 T1.FA_TIPO_CAMB,           
 'Canje', T1.FE_CANJ, 2          
From   TCCANJ_PROV T1 , TDCANJ_PROV T2, TMDOCU_PROV T4, TTDOCU_CNTB T5,          
 TMPROV T6          
Where  T2.CO_EMPR = @ISCO_EMPR           
 And T1.CO_EMPR =   T4.CO_EMPR          
 And T1.TI_SITU != 'ANU'          
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_PROV = T2.CO_PROV          
 AND T2.NU_LETR_PROV Is Null          
 And T1.NU_CANJ = T2.NU_CANJ          
 And T2.CO_PROV = T4.CO_PROV          
 And T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU           
 And T2.NU_DOCU_PROV = T4.NU_DOCU_PROV           
 And T4.CO_ESTA_DOCU != 'ANU'           
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR       
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 And T1.FE_CANJ >= @VDFE_EMIS          
 And T1.FE_CANJ <= @VDFE_FINA           
 And T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
          
          
-- DE LA TMLETR_PROV          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, FA_TICA_OPER,           
 FE_OPER, CO_BACA_PAGO,           
 TI_OPER, NU_ORDE)          
Select  T1.CO_PROV, T3.NO_CORT_PROV, T1.FE_EMIS, T1.CO_MONE, T1.FA_TIPO_CAMB,           
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA,           
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA,           
 T1.NU_LETR_PROV,           
 T1.NU_LETR_PROV,           
 '99',          
 T1.FA_TIPO_CAMB,           
 T1.FE_EMIS,           
 T1.NU_CANJ,           
 'Letra', 3          
From   TMLETR_PROV T1, TTDOCU_CNTB T2, TMPROV T3          
Where  T1.CO_EMPR = @ISCO_EMPR           
   And T1.CO_ESTA_DOCU != 'ANU'          
   And T2.TI_DOCU = 'LET'          
   And T2.ST_RETE = 'S'          
   And T1.CO_EMPR = T3.CO_EMPR          
   And T1.CO_PROV = T3.CO_PROV          
 And T3.ST_RETE = 'N'          
 And T3.ST_BCON = 'N'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T1.FE_EMIS >= @VDFE_EMIS          
 And T1.FE_EMIS <= @VDFE_FINA           
 And Not Exists(Select   T5.CO_EMPR           
         From     TCCOMP_RETE T5, TDCOMP_RETE T6          
         Where    T5.CO_EMPR = @ISCO_EMPR          
       And T5.CO_EMPR = T6.CO_EMPR          
       And T5.TI_SITU = 'ACT'          
       And T5.NU_COMP_RETE = T6.NU_COMP_RETE          
       And T1.CO_PROV = T5.CO_PROV          
       And T6.TI_DOCU = 'LET'          
       And T1.NU_LETR_PROV = T6.NU_DOCU)          
          
and exists (          
Select  T5.*          
From   TCCANJ_PROV T8, TDCANJ_PROV T5, TMDOCU_PROV T7          
Where  T5.CO_EMPR = @ISCO_EMPR           
 And T8.CO_EMPR =   T7.CO_EMPR          
 And T8.TI_SITU != 'ANU'          
 And T8.CO_EMPR = T5.CO_EMPR            
 And T8.CO_PROV = T5.CO_PROV          
 And T8.NU_CANJ = T5.NU_CANJ          
 And T5.CO_PROV = T7.CO_PROV          
 And T5.CO_TIPO_DOCU = T7.CO_TIPO_DOCU           
 And T5.NU_DOCU_PROV = T7.NU_DOCU_PROV           
 And T7.CO_ESTA_DOCU != 'ANU'           
 And T8.FE_CANJ >= @VDFE_EMIS          
 And T8.FE_CANJ <= @VDFE_FINA           
 And T7.FE_EMIS >= @VDFE_EMIS          
 And T7.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T7.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T7.CO_IMP1 LIKE 'IG%' OR T7.CO_IMP2 LIKE 'IG%' OR T7.CO_IMP3 LIKE 'IG%' ) AND ( T7.IM_IMP1 > 0 OR T7.IM_IMP2 > 0 OR T7.IM_IMP3 > 0) )          
AND T1.CO_EMPR = T5.CO_EMPR          
AND T1.CO_PROV = T5.CO_PROV          
AND T1.NU_CANJ = T5.NU_CANJ          
)          
          
UNION          
Select  T1.CO_PROV, T3.NO_CORT_PROV, T1.FE_EMIS, T1.CO_MONE, T1.FA_TIPO_CAMB,           
 PatIndex(T1.CO_MONE, 'SOL')*T1.IM_TOTA,           
 PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA,           
 T1.NU_LETR_PROV,           
 T1.NU_LETR_PROV,           
 '99',          
 T1.FA_TIPO_CAMB,           
 T1.FE_EMIS,           
 T1.NU_CANJ,           
 'Letra', 3          
From   TMLETR_PROV T1, TTDOCU_CNTB T2, TMPROV T3          
Where  T1.CO_EMPR = @ISCO_EMPR           
   And T1.CO_ESTA_DOCU != 'ANU'          
   And T2.TI_DOCU = 'LET'          
   And T2.ST_RETE = 'S'          
   And T1.CO_EMPR = T3.CO_EMPR          
   And T1.CO_PROV = T3.CO_PROV          
 And T3.ST_RETE = 'N'          
 And T3.ST_BCON = 'N'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 And T1.FE_EMIS >= @VDFE_EMIS          
 And T1.FE_EMIS <= @VDFE_FINA           
 And Exists(Select  T5.CO_EMPR           
     From    TCCOMP_RETE T5, TDCOMP_RETE T6          
   Where   T5.CO_EMPR = @ISCO_EMPR          
      And T5.CO_EMPR = T6.CO_EMPR          
      And T5.TI_SITU = 'ACT'          
      And T5.NU_COMP_RETE = T6.NU_COMP_RETE          
      And T1.CO_PROV = T5.CO_PROV          
      And T6.TI_DOCU = 'LET'          
      And T1.NU_LETR_PROV = T6.NU_DOCU)          
          
and exists (          
Select  T5.*          
From   TCCANJ_PROV T8, TDCANJ_PROV T5, TMDOCU_PROV T7          
Where  T5.CO_EMPR = @ISCO_EMPR           
 And T8.CO_EMPR =   T7.CO_EMPR          
 And T8.TI_SITU != 'ANU'          
 And T8.CO_EMPR = T5.CO_EMPR            
 And T8.CO_PROV = T5.CO_PROV          
 And T8.NU_CANJ = T5.NU_CANJ          
 And T5.CO_PROV = T7.CO_PROV          
 And T5.CO_TIPO_DOCU = T7.CO_TIPO_DOCU           
 And T5.NU_DOCU_PROV = T7.NU_DOCU_PROV           
 And T7.CO_ESTA_DOCU != 'ANU'           
 And T8.FE_CANJ >= @VDFE_EMIS          
 And T8.FE_CANJ <= @VDFE_FINA           
 And T7.FE_EMIS >= @VDFE_EMIS          
 And T7.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T7.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T7.CO_IMP1 LIKE 'IG%' OR T7.CO_IMP2 LIKE 'IG%' OR T7.CO_IMP3 LIKE 'IG%' ) AND ( T7.IM_IMP1 > 0 OR T7.IM_IMP2 > 0 OR T7.IM_IMP3 > 0) )          
AND T1.CO_EMPR = T5.CO_EMPR          
AND T1.CO_PROV = T5.CO_PROV          
AND T1.NU_CANJ = T5.NU_CANJ          
)          
          
          
-- D I F E R E N C I A   D E   C A M B I O   P O R   C A N J E S          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA, FA_TICA_PAGO,           
 IM_DIFE_DEBE, IM_DIFE_HABER,           
 FA_TICA_OPER, TI_OPER, FE_OPER, NU_ORDE)          
          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T1.FE_CANJ, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 SubString(T2.NU_DOCU_PROV, 1, Patindex('%-%', T2.NU_DOCU_PROV) - 1),           
 SubString(T2.NU_DOCU_PROV, Patindex('%-%', T2.NU_DOCU_PROV) + 1, Len(T2.NU_DOCU_PROV)),          
 T5.CO_SUNA, T1.NU_CANJ, T1.CO_MONE, T1.FE_CANJ, T1.FA_TIPO_CAMB,           
 Case When T5.CO_SUNA != '07' Then           
  Case When Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T1.FA_TIPO_CAMB)          
  Then Patindex(T1.CO_MONE, 'DOL')*( (IsNull( T2.IM_CANJ_MDOC, 0 )*T4.FA_TIPO_CAMB) - (IsNull( T2.IM_CANJ_MDOC, 0 )*T1.FA_TIPO_CAMB) )          
  Else 0 End          
 Else          
  Case When Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T1.FA_TIPO_CAMB)          
  Then Patindex(T1.CO_MONE, 'DOL')*( (IsNull( T2.IM_CANJ_MDOC, 0 )*T1.FA_TIPO_CAMB) - (IsNull( T2.IM_CANJ_MDOC, 0 )*T4.FA_TIPO_CAMB) )          
  Else 0 End           
 End,          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T1.FA_TIPO_CAMB)          
  Then Patindex(T1.CO_MONE, 'DOL')*( (IsNull( T2.IM_CANJ_MDOC, 0 )*T1.FA_TIPO_CAMB) - (IsNull( T2.IM_CANJ_MDOC, 0 )*T4.FA_TIPO_CAMB) )          
  Else 0 End           
 Else          
  Case When Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > Patindex(T1.CO_MONE, 'DOL')*(T4.IM_TOTA*T1.FA_TIPO_CAMB)          
  Then Patindex(T1.CO_MONE, 'DOL')*( (IsNull( T2.IM_CANJ_MDOC, 0 )*T4.FA_TIPO_CAMB) - (IsNull( T2.IM_CANJ_MDOC, 0 )*T1.FA_TIPO_CAMB) )          
  Else 0 End          
 End,             
          
 T1.FA_TIPO_CAMB, 'Dif Camb Canje', T1.FE_CANJ, 2.5          
From   TCCANJ_PROV T1 , TDCANJ_PROV T2, TMDOCU_PROV T4, TTDOCU_CNTB T5,          
 TMPROV T6          
Where  T2.CO_EMPR = @ISCO_EMPR           
 And T1.CO_EMPR =   T4.CO_EMPR          
 And T1.TI_SITU != 'ANU'          
And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_PROV = T2.CO_PROV          
 AND T2.NU_LETR_PROV Is Null          
 And T1.NU_CANJ = T2.NU_CANJ          
 And T2.CO_PROV = T4.CO_PROV          
 And T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU           
 And T2.NU_DOCU_PROV = T4.NU_DOCU_PROV           
 And T4.CO_ESTA_DOCU != 'ANU'           
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 And T1.FE_CANJ >= @VDFE_EMIS          
 And T1.FE_CANJ <= @VDFE_FINA           
 And T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
          
/* PAGOS BANCOS/CAJA*/          
          
-- BANCOS          
-- DE LA TMDOCU_PROV          
          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO,           
 FE_PAGO_BACA, FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
          
 NU_CHEQ, FA_TICA_OPER,           
 NU_DOCU_REFE,           
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 Case When T2.NU_CHEQ IS Null Then 'Cargo en Cta.' Else 'Cheque' End,           
 T1.CO_BANC+' '+T1.NU_CNTA_BANC+' '+T1.AA_BNCO+' '+T1.MM_BNCO+' '+T1.NU_COMP_BANC,           
 T7.CO_MONE,           
 T2.FE_OPER,            
 T2.FA_TIPO_CAMB,           
          
 CASE WHEN T5.CO_SUNA = '07' THEN          
 PatIndex(T7.CO_MONE, 'SOL')*T1.IM_CONV_DOCU           
 ELSE 0 END 'DOCU_SOLE',           
          
 CASE WHEN T5.CO_SUNA = '07' THEN          
 PatIndex(T7.CO_MONE, 'DOL')*T1.IM_CONV_DOCU           
 ELSE 0 END 'DOCU_DOLA',           
          
          
 CASE WHEN T5.CO_SUNA != '07' THEN          
 PatIndex(T7.CO_MONE, 'SOL')*T1.IM_CONV_DOCU            
 ELSE 0 END 'PAGO_SOLE',           
          
 CASE WHEN T5.CO_SUNA != '07' THEN          
 PatIndex(T7.CO_MONE, 'DOL')*T1.IM_CONV_DOCU           
 ELSE 0 END 'PAGO_DOLA',          
          
 T2.NU_CHEQ,           
 T2.FA_TIPO_CAMB,            
          
 T5.CO_SUNA+' '+          
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1)+' '+          
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
          
 'Pago por Bancos', T2.FE_OPER, 4          
From   TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,          
       TMPROV T6, TMCNTA_BANC T7          
Where  T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU = 'ACT'           
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_UNID_CONC = T2.CO_UNID_CONC
 And T1.CO_BANC = T2.CO_BANC            
 And T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC            
 And T1.AA_BNCO = T2.AA_BNCO          
 And T1.MM_BNCO = T2.MM_BNCO           
 And T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC           
 And T1.NU_COMP_BANC = T2.NU_COMP_BANC          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_BANC = T3.CO_BANC            
 And T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_ENTI = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_BANC = T7.CO_BANC          
 And T2.NU_CNTA_BANC = T7.NU_CNTA_BANC          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
          
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
--DE LA TMLETR_PROV          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_CORR_DOCU, NU_CORR_PROV,           
 CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO, IM_PAGO_SOLE, IM_PAGO_DOLA,           
 NU_CHEQ, FA_TICA_OPER,           
          
 NU_DOCU_REFE,           
          
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 0,           
 0,           
 T4.NU_LETR_PROV, T4.NU_LETR_PROV,           
 Case When T2.NU_CHEQ IS Null Then 'Cargo en Cta.' Else 'Cheque' End,           
 T1.CO_BANC+' '+T1.NU_CNTA_BANC+' '+T1.AA_BNCO+' '+T1.MM_BNCO+' '+T1.NU_COMP_BANC,           
 T7.CO_MONE,           
 T2.FE_OPER,            
 T2.FA_TIPO_CAMB,           
 PatIndex(T7.CO_MONE, 'SOL')*T1.IM_CONV_DOCU,           
 PatIndex(T7.CO_MONE, 'DOL')*T1.IM_CONV_DOCU,           
 T2.NU_CHEQ,           
 T2.FA_TIPO_CAMB,           
          
 T5.CO_SUNA+' '+T4.NU_LETR_PROV,           
          
 'Pago por Bancos', T2.FE_OPER, 4          
From   TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMLETR_PROV T4, TTDOCU_CNTB T5,          
 TMPROV T6, TMCNTA_BANC T7          
Where   T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU = 'ACT'           
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_BANC = T2.CO_UNID_CONC
 And T1.CO_BANC = T2.CO_BANC            
 And T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC            
 And T1.AA_BNCO = T2.AA_BNCO          
 And T1.MM_BNCO = T2.MM_BNCO           
 And T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC           
 And T1.NU_COMP_BANC = T2.NU_COMP_BANC          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_BANC = T3.CO_BANC            
 And T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = 'LET'          
 And T1.NU_DOCU_ENTI = T4.NU_LETR_PROV          
 And T5.TI_DOCU = 'LET'          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_BANC = T7.CO_BANC          
 And T2.NU_CNTA_BANC = T7.NU_CNTA_BANC          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
          
          
          
-- CAJA          
-- DE LA TMDOCU_PROV          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
          
 FA_TICA_OPER, NU_DOCU_REFE,          
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 'Efectivo',            
 T1.CO_CAJA+' '+T1.AA_CAJA+' '+T1.MM_CAJA+' '+T1.NU_COMP_CAJA,           
 T7.CO_MONE,           
 T2.FE_OPER,           
 T2.FA_TIPO_CAMB,           
          
 CASE WHEN T5.CO_SUNA = '07' THEN          
 PatIndex(T7.CO_MONE, 'SOL')*T1.IM_CONV_DOCU            
 ELSE 0 END 'DOCU_SOLE',       
          
 CASE WHEN T5.CO_SUNA = '07' THEN          
 PatIndex(T7.CO_MONE, 'DOL')*T1.IM_CONV_DOCU            
 ELSE 0 END 'DOCU_DOLA',           
          
          
 CASE WHEN T5.CO_SUNA != '07' THEN          
 PatIndex(T7.CO_MONE, 'SOL')*T1.IM_CONV_DOCU            
 ELSE 0 END 'PAGO_SOLE',           
          
 CASE WHEN T5.CO_SUNA != '07' THEN          
 PatIndex(T7.CO_MONE, 'DOL')*T1.IM_CONV_DOCU            
 ELSE 0 END 'PAGO_DOLA',          
          
          
 T2.FA_TIPO_CAMB,           
          
 T5.CO_SUNA+' '+          
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1)+' '+          
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
          
 'Pago por Caja', T2.FE_OPER, 4          
From    TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCAJA T7           
Where   T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU != 'ANU'          
 And T2.TI_SITU != 'DEV'           
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_CAJA = T2.CO_CAJA          
 And T1.AA_CAJA = T2.AA_CAJA          
 And T1.MM_CAJA = T2.MM_CAJA           
 And T1.TI_MOVI = T2.TI_MOVI          
 And T1.NU_COMP_CAJA = T2.NU_COMP_CAJA          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_CAJA = T3.CO_CAJA          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_ENTI = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_CAJA = T7.CO_CAJA          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
-- DE LA TMLETR_PROV          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_CORR_DOCU, NU_CORR_PROV,           
 CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO, IM_PAGO_SOLE, IM_PAGO_DOLA, FA_TICA_OPER,           
 NU_DOCU_REFE,          
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 0,           
 0,          
 T4.NU_LETR_PROV, T4.NU_LETR_PROV,           
 'Efectivo',            
 T1.CO_CAJA+' '+T1.AA_CAJA+' '+T1.MM_CAJA+' '+T1.NU_COMP_CAJA,           
 T7.CO_MONE,           
 T2.FE_OPER,           
 T2.FA_TIPO_CAMB,           
 PatIndex(T7.CO_MONE, 'SOL')*T1.IM_CONV_DOCU,           
 PatIndex(T7.CO_MONE, 'DOL')*T1.IM_CONV_DOCU,          
 T2.FA_TIPO_CAMB,           
          
 T5.CO_SUNA+' '+T4.NU_LETR_PROV,           
          
 'Pago por Caja', T2.FE_OPER, 4           
From    TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMLETR_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCAJA T7           
Where   T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU != 'ANU'          
 And T2.TI_SITU != 'DEV'            
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_CAJA = T2.CO_CAJA          
 And T1.AA_CAJA = T2.AA_CAJA          
 And T1.MM_CAJA = T2.MM_CAJA           
 And T1.TI_MOVI = T2.TI_MOVI          
 And T1.NU_COMP_CAJA = T2.NU_COMP_CAJA          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_CAJA = T3.CO_CAJA          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = 'LET'          
 And T1.NU_DOCU_ENTI = T4.NU_LETR_PROV          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_CAJA = T7.CO_CAJA          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
          
          
-- Rendiciones por Bancos          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
          
 FA_TICA_OPER, NU_DOCU_REFE,          
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_REND_GAST, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 'Rend x Bancos',            
 T2.NU_REND_GAST,           
-- T7.CO_MONE,           
 T4.CO_MONE,           
 T2.FE_REND_GAST,           
 T3.FA_TIPO_CAMB,           
          
 CASE WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'SOL'          
     Then T1.IM_DOCU *-1          
      WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU * T3.FA_TIPO_CAMB *-1          
      Else  0 End 'DOCU_SOLE',           
          
 CASE WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'SOL'          
      Then (T1.IM_DOCU / T3.FA_TIPO_CAMB) *-1          
      WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU *-1          
      Else  0 End 'DOCU_DOLA',           
          
/*          
 CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'SOL'          
     Then T1.IM_DOCU           
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU * T3.FA_TIPO_CAMB          
      Else  0 End 'PAGO_SOLE',           
  CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'SOL'          
      Then T1.IM_DOCU / T3.FA_TIPO_CAMB          
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU           
      Else  0 End 'PAGO_DOLA',           
*/          
          
 CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'SOL'          
     Then T1.IM_DOCU           
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'SOL'          
      Then T1.IM_DOCU          
      Else  0 End 'PAGO_SOLE',           
          
 CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU           
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU          
      Else  0 End 'PAGO_DOLA',           
          
 T3.FA_TIPO_CAMB,           
 T5.CO_SUNA+' '+          
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1)+' '+          
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
          
 'Rend x Bancos', T2.FE_REND_GAST, 4          
From  TDREND_GAST T1, TCREND_GAST T2, TCMOVI_BANC T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCNTA_BANC T7           
Where T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU != 'ANU'          
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.NU_REND_GAST = T2.NU_REND_GAST          
 And T2.NU_CNTA_DEST Is Not Null          
          
 And T2.CO_EMPR_DEST = T3.CO_EMPR            
 And T2.CO_BANC_DEST = T3.CO_BANC          
 And T2.NU_CNTA_DEST = T3.NU_CNTA_BANC          
          
 And T2.AA_OPER_DEST = T3.AA_BNCO            
 And T2.MM_OPER_DEST = T3.MM_BNCO            
 And T3.TI_MOVI_BANC = 'E'          
 And T2.NU_COMP_DEST = T3.NU_COMP_BANC          
          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.CO_TIPO_DOCU = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_PROV = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
          
 And T2.CO_EMPR = T7.CO_EMPR            
 And T2.CO_BANC_DEST = T7.CO_BANC          
 And T2.NU_CNTA_DEST = T7.NU_CNTA_BANC          
          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
-- Rendiciones x Caja          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
          
 FA_TICA_OPER, NU_DOCU_REFE,          
 TI_OPER, FE_OPER, NU_ORDE)          
          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_REND_GAST, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 'Rend x Caja',            
 T2.NU_REND_GAST,           
 T4.CO_MONE,           
 T2.FE_REND_GAST,           
 T3.FA_TIPO_CAMB,           
          
 CASE WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'SOL'          
     Then T1.IM_DOCU *-1          
      WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU * T3.FA_TIPO_CAMB *-1          
      Else  0 End 'DOCU_SOLE',           
          
 CASE WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'SOL'          
      Then (T1.IM_DOCU / T3.FA_TIPO_CAMB) *-1          
      WHEN T5.CO_SUNA = '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU *-1          
      Else  0 End 'DOCU_DOLA',           
          
/*          
 CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'SOL'          
     Then T1.IM_DOCU           
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU * T3.FA_TIPO_CAMB          
      Else  0 End 'PAGO_SOLE',           
          
 CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'SOL'          
      Then T1.IM_DOCU / T3.FA_TIPO_CAMB          
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU           
      Else  0 End 'PAGO_DOLA',           
*/          
          
 CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'SOL'          
     Then T1.IM_DOCU           
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'SOL'          
      Then T1.IM_DOCU          
      Else  0 End 'PAGO_SOLE',           
          
 CASE WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'DOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU           
      WHEN T5.CO_SUNA != '07' And  T7.CO_MONE  =  'SOL' And T4.CO_MONE  = 'DOL'          
       Then T1.IM_DOCU          
      Else  0 End 'PAGO_DOLA',           
          
 T3.FA_TIPO_CAMB,           
          
 T5.CO_SUNA+' '+          
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1)+' '+          
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
          
 'Rend x Caja', T2.FE_REND_GAST, 4          
From  TDREND_GAST T1, TCREND_GAST T2, TCMOVI_CAJA T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCAJA T7           
Where T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU != 'ANU'          
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.NU_REND_GAST = T2.NU_REND_GAST          
 And T2.CO_CAJA_DEST Is Not Null          
          
 And T2.CO_EMPR_DEST = T3.CO_EMPR            
 And T2.CO_CAJA_DEST = T3.CO_CAJA          
 And T2.AA_OPER_DEST = T3.AA_CAJA            
 And T2.MM_OPER_DEST = T3.MM_CAJA            
 And T3.TI_MOVI   = 'E'          
 And T2.NU_COMP_DEST = T3.NU_COMP_CAJA          
          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.CO_TIPO_DOCU = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_PROV = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_CAJA_DEST = T7.CO_CAJA          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
          
/* A J U S T E   X  D I F E R E N C I A     C A M B I O   R E N D I C I O N E S*/          
          
-- POR BANCOS          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO,           
 FE_PAGO_BACA, FA_TICA_PAGO,           
          
 IM_DIFE_DEBE, IM_DIFE_HABER,           
          
 FA_TICA_OPER, TI_OPER, FE_OPER, NU_ORDE)          
Select T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_REND_GAST, T4.CO_MONE, T4.FA_TIPO_CAMB,           
  0,          
  0,          
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 T5.CO_SUNA,           
 T2.NU_REND_GAST,           
          
 T7.CO_MONE,           
 T2.FE_REND_GAST,           
 T3.FA_TIPO_CAMB,           
          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_DOCU*T3.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T3.FA_TIPO_CAMB) - (T1.IM_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T3.FA_TIPO_CAMB) - (T1.IM_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_DOCU*T3.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
 T3.FA_TIPO_CAMB,           
 'Dif Camb Rend', T2.FE_REND_GAST, 6          
          
From  TDREND_GAST T1, TCREND_GAST T2, TCMOVI_BANC T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCNTA_BANC T7           
Where T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU != 'ANU'          
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.NU_REND_GAST = T2.NU_REND_GAST          
 And T2.NU_CNTA_DEST Is Not Null          
          
 And T2.CO_EMPR_DEST = T3.CO_EMPR            
 And T2.CO_BANC_DEST = T3.CO_BANC          
 And T2.NU_CNTA_DEST = T3.NU_CNTA_BANC          
          
 And T2.AA_OPER_DEST = T3.AA_BNCO            
 And T2.MM_OPER_DEST = T3.MM_BNCO            
 And T3.TI_MOVI_BANC = 'E'          
 And T2.NU_COMP_DEST = T3.NU_COMP_BANC          
          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.CO_TIPO_DOCU = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_PROV = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
          
 And T2.CO_EMPR = T7.CO_EMPR           
 And T2.CO_BANC_DEST = T7.CO_BANC          
 And T2.NU_CNTA_DEST = T7.NU_CNTA_BANC          
          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
          
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
-- CAJA          
IF @ISST_RETE = 'N'          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO,           
 FE_PAGO_BACA, FA_TICA_PAGO,           
          
 IM_DIFE_DEBE, IM_DIFE_HABER,           
          
 FA_TICA_OPER, TI_OPER, FE_OPER, NU_ORDE)          
          
          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_REND_GAST, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 0,          
 0,          
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 T5.CO_SUNA,           
 T2.NU_REND_GAST,           
 T7.CO_MONE,           
 T2.FE_REND_GAST,       
 T3.FA_TIPO_CAMB,           
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_DOCU*T3.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T3.FA_TIPO_CAMB) - (T1.IM_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T3.FA_TIPO_CAMB) - (T1.IM_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T4.CO_MONE, 'DOL')*(T4.IM_TOTA*T3.FA_TIPO_CAMB)          
  Then PatIndex(T4.CO_MONE, 'DOL')*( (T1.IM_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_DOCU*T3.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
 T3.FA_TIPO_CAMB,           
 'Dif Camb Rend', T2.FE_REND_GAST, 6          
          
From  TDREND_GAST T1, TCREND_GAST T2, TCMOVI_CAJA T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCAJA T7           
Where T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'      
 And T2.TI_SITU != 'ANU'          
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.NU_REND_GAST = T2.NU_REND_GAST          
 And T2.CO_CAJA_DEST Is Not Null          
          
 And T2.CO_EMPR_DEST = T3.CO_EMPR            
 And T2.CO_CAJA_DEST = T3.CO_CAJA          
 And T2.AA_OPER_DEST = T3.AA_CAJA            
 And T2.MM_OPER_DEST = T3.MM_CAJA            
 And T3.TI_MOVI   = 'E'          
 And T2.NU_COMP_DEST = T3.NU_COMP_CAJA          
          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.CO_TIPO_DOCU = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_PROV = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_CAJA_DEST = T7.CO_CAJA          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
          
/* A J U S T E    P O R    D I F E R E N C I A    D E    C A M B I O */          
          
-- BANCOS          
-- DE LA TMDOCU_PROV          
          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO,           
 FE_PAGO_BACA, FA_TICA_PAGO,           
          
 IM_DIFE_DEBE, IM_DIFE_HABER,           
          
 FA_TICA_OPER, TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 0,           
 0,           
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 T5.CO_SUNA,           
 T1.CO_BANC+' '+T1.NU_CNTA_BANC+' '+T1.AA_BNCO+' '+T1.MM_BNCO+' '+T1.NU_COMP_BANC,           
 T7.CO_MONE,           
 T2.FE_OPER,            
 T2.FA_TIPO_CAMB,           
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 End,       
          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
 T2.FA_TIPO_CAMB,           
 'Dif Camb Pago', T2.FE_OPER, 6          
From   TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,          
  TMPROV T6, TMCNTA_BANC T7          
Where   T1.CO_EMPR = @ISCO_EMPR          
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU = 'ACT'           
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_UNID_CONC = T2.CO_UNID_CONC
 And T1.CO_BANC = T2.CO_BANC            
 And T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC            
 And T1.AA_BNCO = T2.AA_BNCO          
 And T1.MM_BNCO = T2.MM_BNCO           
 And T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC           
 And T1.NU_COMP_BANC = T2.NU_COMP_BANC          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_BANC = T3.CO_BANC            
 And T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_ENTI = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_BANC = T7.CO_BANC          
 And T2.NU_CNTA_BANC = T7.NU_CNTA_BANC          
 --And T2.FA_TIPO_CAMB != T4.FA_TIPO_CAMB          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
          
--DE LA TMLETR_PROV          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_CORR_DOCU,           
 CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO, IM_DIFE_DEBE, IM_DIFE_HABER, FA_TICA_OPER,           
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 0,           
 0,           
 T4.NU_LETR_PROV,           
 T5.CO_SUNA,           
 T1.CO_BANC+' '+T1.NU_CNTA_BANC+' '+T1.AA_BNCO+' '+T1.MM_BNCO+' '+T1.NU_COMP_BANC,           
 T7.CO_MONE,           
 T2.FE_OPER,            
 T2.FA_TIPO_CAMB,            
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
 T2.FA_TIPO_CAMB,           
 'Dif Camb Pago', T2.FE_OPER, 6          
From   TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMLETR_PROV T4, TTDOCU_CNTB T5,          
 TMPROV T6, TMCNTA_BANC T7          
Where   T1.CO_EMPR = @ISCO_EMPR           
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU = 'ACT'           
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_UNID_CONC = T2.CO_UNID_CONC
 And T1.CO_BANC = T2.CO_BANC            
 And T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC            
 And T1.AA_BNCO = T2.AA_BNCO          
 And T1.MM_BNCO = T2.MM_BNCO           
 And T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC           
 And T1.NU_COMP_BANC = T2.NU_COMP_BANC          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_BANC = T3.CO_BANC            
 And T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = 'LET'          
 And T1.NU_DOCU_ENTI = T4.NU_LETR_PROV          
 And T5.TI_DOCU = 'LET'          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_BANC = T7.CO_BANC          
 And T2.NU_CNTA_BANC = T7.NU_CNTA_BANC          
-- And T2.FA_TIPO_CAMB != T4.FA_TIPO_CAMB          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
          
          
-- CAJA          
-- DE LA TMDOCU_PROV          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO, IM_DIFE_DEBE, IM_DIFE_HABER, FA_TICA_OPER,           
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 0,           
 0,           
 SubString(T4.NU_DOCU_PROV, 1, Patindex('%-%', T4.NU_DOCU_PROV) - 1),           
 SubString(T4.NU_DOCU_PROV, Patindex('%-%', T4.NU_DOCU_PROV) + 1, Len(T4.NU_DOCU_PROV)),           
 T4.NU_CORR_COMP,           
 T5.CO_SUNA,           
 T1.CO_CAJA+' '+T1.AA_CAJA+' '+T1.MM_CAJA+' '+T1.NU_COMP_CAJA,           
 T7.CO_MONE,           
 T2.FE_OPER,           
 T2.FA_TIPO_CAMB,            
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
 T2.FA_TIPO_CAMB,           
 'Dif Camb Pago', T2.FE_OPER, 6          
From    TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMDOCU_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCAJA T7           
Where   T1.CO_EMPR = @ISCO_EMPR           
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU != 'ANU'          
 And T2.TI_SITU != 'DEV'           
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_CAJA = T2.CO_CAJA          
 And T1.AA_CAJA = T2.AA_CAJA          
 And T1.MM_CAJA = T2.MM_CAJA           
 And T1.TI_MOVI = T2.TI_MOVI          
 And T1.NU_COMP_CAJA = T2.NU_COMP_CAJA          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_CAJA = T3.CO_CAJA          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU          
 And T1.NU_DOCU_ENTI = T4.NU_DOCU_PROV          
 And T4.CO_TIPO_DOCU = T5.TI_DOCU          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_CAJA = T7.CO_CAJA          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T4.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
 AND ( ( T4.CO_IMP1 LIKE 'IG%' OR T4.CO_IMP2 LIKE 'IG%' OR T4.CO_IMP3 LIKE 'IG%' ) AND ( T4.IM_IMP1 > 0 OR T4.IM_IMP2 > 0 OR T4.IM_IMP3 > 0) )          
          
          
-- DE LA TMLETR_PROV          
Insert Into #TWREGI_RETE_Q01(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 IM_DOCU_SOLE, IM_DOCU_DOLA, NU_CORR_DOCU,           
 CO_SUNA_DOCU, CO_BACA_PAGO, CO_MONE_PAGO, FE_PAGO_BACA,           
 FA_TICA_PAGO, IM_DIFE_DEBE, IM_DIFE_HABER, FA_TICA_OPER,           
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T4.CO_PROV, T6.NO_CORT_PROV, T2.FE_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,           
 0,           
 0,           
 T4.NU_LETR_PROV,           
 T5.CO_SUNA,           
 T1.CO_CAJA+' '+T1.AA_CAJA+' '+T1.MM_CAJA+' '+T1.NU_COMP_CAJA,           
 T7.CO_MONE,           
 T2.FE_OPER,           
 T2.FA_TIPO_CAMB,           
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
          
 Case When T5.CO_SUNA != '07' Then           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) < PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) )          
  Else 0 End          
 Else           
  Case When PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T4.FA_TIPO_CAMB) > PatIndex(T7.CO_MONE, 'DOL')*(T4.IM_TOTA*T2.FA_TIPO_CAMB)          
  Then PatIndex(T7.CO_MONE, 'DOL')*( (T1.IM_CONV_DOCU*T4.FA_TIPO_CAMB) - (T1.IM_CONV_DOCU*T2.FA_TIPO_CAMB) )          
  Else 0 End          
 End,          
          
 T2.FA_TIPO_CAMB,           
 'Dif Camb Pago', T2.FE_OPER, 6          
From  TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMLETR_PROV T4, TTDOCU_CNTB T5,           
 TMPROV T6, TMCAJA T7           
Where   T1.CO_EMPR = @ISCO_EMPR           
 And T1.TI_ENTI = 'P'          
 And T2.TI_SITU != 'ANU'          
 And T2.TI_SITU != 'DEV'            
 And T1.CO_EMPR = T2.CO_EMPR            
 And T1.CO_CAJA = T2.CO_CAJA          
 And T1.AA_CAJA = T2.AA_CAJA          
 And T1.MM_CAJA = T2.MM_CAJA           
 And T1.TI_MOVI = T2.TI_MOVI          
 And T1.NU_COMP_CAJA = T2.NU_COMP_CAJA          
 And T2.FE_OPER <= @VDFE_FINA           
 And T1.CO_EMPR = T3.CO_EMPR            
 And T1.CO_CAJA = T3.CO_CAJA          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_ENTI = T4.CO_PROV          
 And T1.TI_DOCU_ENTI = 'LET'          
 And T1.NU_DOCU_ENTI = T4.NU_LETR_PROV          
 And T5.ST_RETE = 'S'          
   And T4.CO_EMPR = T6.CO_EMPR          
   And T4.CO_PROV = T6.CO_PROV          
 And T6.ST_RETE = 'N'          
 And T6.ST_BCON = 'N'          
 And T2.CO_EMPR = T7.CO_EMPR          
 And T2.CO_CAJA = T7.CO_CAJA          
 --And T2.FA_TIPO_CAMB != T4.FA_TIPO_CAMB          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T6.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T4.FE_EMIS >= @VDFE_EMIS          
 And T4.FE_EMIS <= @VDFE_FINA           
          
          
/* C O M P R O B A N T E S    D E    R E T E N C I O N */          
-- BANCOS          
-- DE LA TMDOCU_PROV          
          
Insert Into #TWREGI_RETE_Q02(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
 CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,            
          
 IM_RETE_SOLE, IM_RETE_DOLA, IM_TORE_SOLE,           
 IM_TORE_DOLA, NU_CHEQ, FA_TICA_OPER,           
 NU_DOCU_REFE,          
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T3.CO_PROV, T1.NO_PROV, T1.FE_COMP_RETE, T3.CO_MONE, T3.FA_TIPO_CAMB,           
 SubString(T3.NU_DOCU_PROV, 1, Patindex('%-%', T3.NU_DOCU_PROV) - 1),          
 SubString(T3.NU_DOCU_PROV, Patindex('%-%', T3.NU_DOCU_PROV) + 1, Len(T3.NU_DOCU_PROV)),          
 T3.NU_CORR_COMP, T1.FE_COMP_RETE,  
 Case When T4.NU_CHEQ IS Null Then 'Cargo en Cta.' Else 'Cheque' End,           
 T4.CO_BANC+' '+T4.NU_CNTA_BANC+' '+T4.AA_BNCO+' '+T4.MM_BNCO+' '+ Isnull(T4.NU_COMP_BANC, ''),  
 T3.CO_MONE,           
 SubString(T2.NU_COMP_RETE, 1, Patindex('%-%', T2.NU_COMP_RETE) - 1),          
 SubString(T2.NU_COMP_RETE, Patindex('%-%', T2.NU_COMP_RETE) + 1, Len(T2.NU_COMP_RETE)),          
 T1.FA_TIPO_CAMB,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 PatIndex(T3.CO_MONE, 'SOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * T2.IM_RETE_CONV,           
 PatIndex(T3.CO_MONE, 'DOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * ROUND(T2.IM_RETE_CONV / T1.FA_TIPO_CAMB, 2),    
          
 PatIndex(T3.CO_MONE, 'SOL')*T5.IM_RETE,           
 PatIndex(T3.CO_MONE, 'DOL')* ROUND(T5.IM_RETE / T1.FA_TIPO_CAMB, 2),           
          
 T4.NU_CHEQ, T1.FA_TIPO_CAMB,           
           
 T8.CO_SUNA+' '+          
 SubString(T3.NU_DOCU_PROV, 1, Patindex('%-%', T3.NU_DOCU_PROV) - 1)+' '+          
 SubString(T3.NU_DOCU_PROV, Patindex('%-%', T3.NU_DOCU_PROV) + 1, Len(T3.NU_DOCU_PROV)),           
          
 'Pago/Retencion', T1.FE_COMP_RETE, 5          
From  TCCOMP_RETE T1, TDCOMP_RETE T2, TMDOCU_PROV T3, TCMOVI_BANC T4,          
 (Select   T1.NU_COMP_RETE 'NU_COMP_RETE',           
    Sum (( Patindex( T3.ST_SIGN, 'S' ) - Patindex( T3.ST_SIGN, 'N' )) * T2.IM_RETE_CONV) 'IM_RETE'          
  From    TCCOMP_RETE T1, TDCOMP_RETE T2, TTDOCU_CNTB T3          
  Where   T1.CO_EMPR = @ISCO_EMPR           
    And T1.TI_SITU = 'ACT'            
    And T1.CO_EMPR = T2.CO_EMPR          
    And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
    And T2.TI_DOCU = T3.TI_DOCU          
  Group By  T1.NU_COMP_RETE) T5,          
 TMEMPR T6, TTMONE T7, TTDOCU_CNTB T8          
Where T1.CO_EMPR = @ISCO_EMPR           
 And T1.CO_EMPR = T2.CO_EMPR          
 And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
 And T1.CO_EMPR = T3.CO_EMPR          
 And T1.CO_PROV = T3.CO_PROV          
 And T2.TI_DOCU = T3.CO_TIPO_DOCU          
 And T2.NU_DOCU = T3.NU_DOCU_PROV          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_BANC = T4.CO_BANC          
 And T1.NU_CNTA_BANC  = T4.NU_CNTA_BANC          
 And T1.AA_BACA = T4.AA_BNCO          
 And T1.MM_BACA = T4.MM_BNCO          
 And T4.TI_MOVI_BANC = 'E'          
 And T1.NU_COMP_BACA = T4.NU_COMP_BANC          
 And T1.NU_COMP_RETE = T5.NU_COMP_RETE          
 And T1.CO_EMPR = T6.CO_EMPR          
 And T1.CO_MONE = T7.CO_MONE          
 And T3.CO_TIPO_DOCU = T8.TI_DOCU          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T3.FE_EMIS >= @VDFE_EMIS          
 And T3.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T3.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
--AND ( ( T3.CO_IMP1 = 'IGV' OR T3.CO_IMP2 = 'IGV' OR T3.CO_IMP3 = 'IGV' ) AND ( T3.IM_IMP1 > 0 OR T3.IM_IMP2 > 0 OR T3.IM_IMP3 > 0) )         
  
-- DE LA TMLETR_PROV          
Insert Into #TWREGI_RETE_Q02(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
 CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,            
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
          
 IM_RETE_SOLE, IM_RETE_DOLA,           
 IM_TORE_SOLE, IM_TORE_DOLA,           
 NU_CHEQ, FA_TICA_OPER,           
 NU_DOCU_REFE,           
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T3.CO_PROV, T1.NO_PROV, T1.FE_COMP_RETE, T3.CO_MONE, T3.FA_TIPO_CAMB,           
 Null,           
 T3.NU_LETR_PROV,           
 Null, T4.FE_OPER,           
 Case When T4.NU_CHEQ IS Null Then 'Cargo en Cta.' Else 'Cheque' End,           
 T4.CO_BANC+' '+T4.NU_CNTA_BANC+' '+T4.AA_BNCO+' '+T4.MM_BNCO+' '+T4.NU_COMP_BANC,           
 T3.CO_MONE,           
 SubString(T2.NU_COMP_RETE, 1, Patindex('%-%', T2.NU_COMP_RETE) - 1),          
 SubString(T2.NU_COMP_RETE, Patindex('%-%', T2.NU_COMP_RETE) + 1, Len(T2.NU_COMP_RETE)),           
 T4.FA_TIPO_CAMB,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,          
          
 PatIndex(T3.CO_MONE, 'SOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * T2.IM_RETE_CONV,           
 PatIndex(T3.CO_MONE, 'DOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * T2.IM_RETE_CONV,           
          
 PatIndex(T3.CO_MONE, 'SOL')*T5.IM_RETE,           
 PatIndex(T3.CO_MONE, 'DOL')*T5.IM_RETE,           
 T4.NU_CHEQ, T4.FA_TIPO_CAMB,           
           
 T8.CO_SUNA+' '+T3.NU_LETR_PROV,           
          
 'Pago/Retencion', T1.FE_COMP_RETE, 5          
          
From  TCCOMP_RETE T1, TDCOMP_RETE T2, TMLETR_PROV T3, TCMOVI_BANC T4,          
 (Select  T1.NU_COMP_RETE 'NU_COMP_RETE',           
   Sum (( Patindex( T3.ST_SIGN, 'S' ) - Patindex( T3.ST_SIGN, 'N' )) * T2.IM_RETE_CONV) 'IM_RETE'          
 From   TCCOMP_RETE T1, TDCOMP_RETE T2, TTDOCU_CNTB T3          
 Where  T1.CO_EMPR = @ISCO_EMPR           
   And T1.TI_SITU = 'ACT'            
   And T1.CO_EMPR = T2.CO_EMPR          
   And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
   And T2.TI_DOCU = T3.TI_DOCU          
 Group By T1.NU_COMP_RETE) T5,          
 TMEMPR T6, TTMONE T7, TTDOCU_CNTB T8          

Where T1.CO_EMPR = @ISCO_EMPR           
 And T1.CO_EMPR = T2.CO_EMPR          
 And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
 And T1.CO_EMPR = T3.CO_EMPR          
 And T1.CO_PROV = T3.CO_PROV          
 And T2.TI_DOCU = 'LET'          
 And T2.NU_DOCU = T3.NU_LETR_PROV          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_BANC = T4.CO_BANC          
 And T1.NU_CNTA_BANC  = T4.NU_CNTA_BANC          
 And T1.AA_BACA = T4.AA_BNCO          
 And T1.MM_BACA = T4.MM_BNCO          
 And T4.TI_MOVI_BANC = 'E'          
 And T1.NU_COMP_BACA = T4.NU_COMP_BANC          
 And T1.NU_COMP_RETE = T5.NU_COMP_RETE          
 And T1.CO_EMPR = T6.CO_EMPR          
 And T1.CO_MONE = T7.CO_MONE          
 And T8.TI_DOCU = 'LET'          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T3.FE_EMIS >= @VDFE_EMIS          
 And T3.FE_EMIS <= @VDFE_FINA           
          
          
          
-- CAJA          
-- DE LA TMDOCU_PROV                
Insert Into #TWREGI_RETE_Q02(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
 CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,            
 IM_PAGO_SOLE, IM_PAGO_DOLA,           
          
 IM_RETE_SOLE, IM_RETE_DOLA,           
 IM_TORE_SOLE, IM_TORE_DOLA,           
 NU_CHEQ, FA_TICA_OPER, NU_DOCU_REFE,           
 TI_OPER, FE_OPER, NU_ORDE)          
          
Select  T3.CO_PROV, T1.NO_PROV, T1.FE_COMP_RETE, T3.CO_MONE, T3.FA_TIPO_CAMB,           
 SubString(T3.NU_DOCU_PROV, 1, Patindex('%-%', T3.NU_DOCU_PROV) - 1),          
 SubString(T3.NU_DOCU_PROV, Patindex('%-%', T3.NU_DOCU_PROV) + 1, Len(T3.NU_DOCU_PROV)),          
 T3.NU_CORR_COMP, T4.FE_OPER,           
 'Efectivo',           
 T4.CO_CAJA+' '+T4.AA_CAJA+' '+T4.MM_CAJA+' '+T4.NU_COMP_CAJA,           
 T3.CO_MONE,           
 SubString(T2.NU_DOCU, 1, Patindex('%-%', T2.NU_DOCU) - 1),          
 SubString(T2.NU_DOCU, Patindex('%-%', T2.NU_DOCU) + 1, Len(T2.NU_DOCU)),          
 T4.FA_TIPO_CAMB,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 PatIndex(T3.CO_MONE, 'SOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * T2.IM_RETE_CONV,           
 PatIndex(T3.CO_MONE, 'DOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * T2.IM_RETE_CONV,           
  PatIndex(T3.CO_MONE, 'SOL')*T5.IM_RETE,           
 PatIndex(T3.CO_MONE, 'DOL')*T5.IM_RETE,           
 Null, T4.FA_TIPO_CAMB,           
          
 T8.CO_SUNA+' '+          
 SubString(T3.NU_DOCU_PROV, 1, Patindex('%-%', T3.NU_DOCU_PROV) - 1)+' '+          
 SubString(T3.NU_DOCU_PROV, Patindex('%-%', T3.NU_DOCU_PROV) + 1, Len(T3.NU_DOCU_PROV)),           
          
 'Pago/Retencion', T1.FE_COMP_RETE, 5          
          
From  TCCOMP_RETE T1, TDCOMP_RETE T2, TMDOCU_PROV T3, TCMOVI_CAJA T4,       
 (Select  T1.NU_COMP_RETE As NU_COMP_RETE,          
   Sum (( Patindex( T3.ST_SIGN, 'S'  ) - Patindex( T3.ST_SIGN, 'N'  )) * T2.IM_RETE_CONV) As IM_RETE          
  From   TCCOMP_RETE T1, TDCOMP_RETE T2, TTDOCU_CNTB T3          
  Where  T1.CO_EMPR = @ISCO_EMPR           
   And T1.TI_SITU = 'ACT'          
   And T1.CO_EMPR = T2.CO_EMPR          
   And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
   And T2.TI_DOCU = T3.TI_DOCU          
 Group By T1.NU_COMP_RETE) T5,          
 TMEMPR T6, TTMONE T7, TTDOCU_CNTB T8          
Where T1.CO_EMPR = @ISCO_EMPR           
 And T1.CO_EMPR = T2.CO_EMPR          
 And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
 And T1.CO_EMPR = T3.CO_EMPR          
 And T1.CO_PROV = T3.CO_PROV          
 And T2.TI_DOCU = T3.CO_TIPO_DOCU          
 And T2.NU_DOCU = T3.NU_DOCU_PROV          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_CAJA = T4.CO_CAJA          
 And T1.AA_BACA = T4.AA_CAJA          
 And T1.MM_BACA = T4.MM_CAJA          
 And T4.TI_MOVI = 'E'          
 And T1.NU_COMP_BACA = T4.NU_COMP_CAJA          
 And T1.NU_COMP_RETE = T5.NU_COMP_RETE          
 And T1.CO_EMPR = T6.CO_EMPR          
 And T1.CO_MONE = T7.CO_MONE          
 And T3.CO_TIPO_DOCU = T8.TI_DOCU          
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T3.FE_EMIS >= @VDFE_EMIS          
 And T3.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T3.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
          
-- AND ( ( T3.CO_IMP1 = 'IGV' OR T3.CO_IMP2 = 'IGV' OR T3.CO_IMP3 = 'IGV' ) AND ( T3.IM_IMP1 > 0 OR T3.IM_IMP2 > 0 OR T3.IM_IMP3 > 0) )          
          
          
-- DE LA TMLETR_PROV          
          
Insert Into #TWREGI_RETE_Q02(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,           
 NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
 CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
           
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,        
          
 IM_RETE_SOLE, IM_RETE_DOLA,           
 IM_TORE_SOLE, IM_TORE_DOLA,           
 NU_CHEQ, FA_TICA_OPER,           
 NU_DOCU_REFE,           
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T3.CO_PROV, T1.NO_PROV, T1.FE_COMP_RETE, T3.CO_MONE, T3.FA_TIPO_CAMB,           
 Null,           
 T3.NU_LETR_PROV,           
 Null, T4.FE_OPER,           
 'Efectivo',           
 T4.CO_CAJA+' '+T4.AA_CAJA+' '+T4.MM_CAJA+' '+T4.NU_COMP_CAJA,           
 T3.CO_MONE,           
 Null,           
 T2.NU_DOCU,           
 T4.FA_TIPO_CAMB,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA = '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'SOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 CASE WHEN T8.CO_SUNA != '07' THEN          
 PatIndex(T3.CO_MONE, 'DOL')*T2.IM_PAGO_CONV          
 ELSE 0 END,           
          
 PatIndex(T3.CO_MONE, 'SOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * T2.IM_RETE_CONV,           
 PatIndex(T3.CO_MONE, 'DOL') * ( Patindex( T8.ST_SIGN, 'S' ) - Patindex( T8.ST_SIGN, 'N' )) * T2.IM_RETE_CONV,           
          
 PatIndex(T3.CO_MONE, 'SOL')*T5.IM_RETE,           
 PatIndex(T3.CO_MONE, 'DOL')*T5.IM_RETE,           
 Null, T4.FA_TIPO_CAMB,          
           
 T8.CO_SUNA+' '+T3.NU_LETR_PROV,           
          
 'Pago/Retencion', T1.FE_COMP_RETE, 5          
          
From  TCCOMP_RETE T1, TDCOMP_RETE T2, TMLETR_PROV T3, TCMOVI_CAJA T4,          
 (Select   T1.NU_COMP_RETE As NU_COMP_RETE,          
    Sum (( Patindex( T3.ST_SIGN, 'S'  ) - Patindex( T3.ST_SIGN, 'N'  )) * T2.IM_RETE_CONV) As IM_RETE          
  From    TCCOMP_RETE T1, TDCOMP_RETE T2, TTDOCU_CNTB T3          
  Where   T1.CO_EMPR = @ISCO_EMPR           
    And T1.TI_SITU = 'ACT'          
    And T1.CO_EMPR = T2.CO_EMPR          
    And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
    And T2.TI_DOCU = T3.TI_DOCU          
  Group By T1.NU_COMP_RETE) T5,          
 TMEMPR T6, TTMONE T7, TTDOCU_CNTB T8          
Where T1.CO_EMPR = @ISCO_EMPR           
 And T1.CO_EMPR = T2.CO_EMPR          
 And T1.NU_COMP_RETE = T2.NU_COMP_RETE          
 And T1.CO_EMPR = T3.CO_EMPR          
 And T1.CO_PROV = T3.CO_PROV          
 And T2.TI_DOCU = 'LET'          
 And T2.NU_DOCU = T3.NU_LETR_PROV          
 And T1.CO_EMPR = T4.CO_EMPR          
 And T1.CO_CAJA = T4.CO_CAJA          
 And T1.AA_BACA = T4.AA_CAJA          
 And T1.MM_BACA = T4.MM_CAJA          
 And T4.TI_MOVI = 'E'          
 And T1.NU_COMP_BACA = T4.NU_COMP_CAJA          
 And T1.NU_COMP_RETE = T5.NU_COMP_RETE          
 And T1.CO_EMPR = T6.CO_EMPR          
 And T1.CO_MONE = T7.CO_MONE          
 And T8.TI_DOCU = 'LET'           
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )           
 AND T3.FE_EMIS >= @VDFE_EMIS          
 And T3.FE_EMIS <= @VDFE_FINA           
          
If @ISST_RETE = 'S'    
-- Comp. de Retenci½n Anulados    
Insert Into #TWREGI_RETE_Q02(          
 CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
 NU_SERI_DOCU, NU_CORR_DOCU,   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
 CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
          
 IM_DOCU_SOLE, IM_DOCU_DOLA,           
 IM_PAGO_SOLE, IM_PAGO_DOLA,            
          
 IM_RETE_SOLE, IM_RETE_DOLA, IM_TORE_SOLE,           
 IM_TORE_DOLA, NU_CHEQ, FA_TICA_OPER,           
 NU_DOCU_REFE,          
 TI_OPER, FE_OPER, NU_ORDE)          
Select  T3.CO_PROV, T1.NO_PROV, T1.FE_COMP_RETE, T3.CO_MONE, T3.FA_TIPO_CAMB,           
 '', '',     
 T3.NU_CORR_COMP, T1.FE_COMP_RETE, 'ANULADO', '',    
 /*Case When T4.NU_CHEQ IS Null Then 'Cargo en Cta.' Else 'Cheque' End,           
 T4.CO_BANC+' '+T4.NU_CNTA_BANC+' '+T4.AA_BNCO+' '+T4.MM_BNCO+' '+T4.NU_COMP_BANC,*/    
 T3.CO_MONE,           
 SubString(T1.NU_COMP_RETE, 1, Patindex('%-%', T1.NU_COMP_RETE) - 1),          
 SubString(T1.NU_COMP_RETE, Patindex('%-%', T1.NU_COMP_RETE) + 1, Len(T1.NU_COMP_RETE)),          
 T3.FA_TIPO_CAMB,           
          
 0, 0, 0, 0, 0, 0, 0, 0,     
                
 '', T3.FA_TIPO_CAMB,           
           
 T8.CO_SUNA+' '+          
 SubString(T3.NU_DOCU_PROV, 1, Patindex('%-%', T3.NU_DOCU_PROV) - 1)+' '+          
 SubString(T3.NU_DOCU_PROV, Patindex('%-%', T3.NU_DOCU_PROV) + 1, Len(T3.NU_DOCU_PROV)),           
          
 'Pago/Retencion', T1.FE_COMP_RETE, 5          
From  TCCOMP_RETE T1, TMDOCU_PROV T3, TMEMPR T6, TTMONE T7, TTDOCU_CNTB T8          
Where T1.CO_EMPR = @ISCO_EMPR           
 And T1.TI_SITU = 'ANU'    
 And T1.CO_EMPR = T3.CO_EMPR          
 And T1.CO_PROV = T3.CO_PROV    
 And T3.CO_TIPO_DOCU = T8.TI_DOCU    
 And T8.ST_DOCU_0021 = 'S'    
 And T1.NU_COMP_RETE = T3.NU_DOCU_PROV          
 And T1.CO_EMPR = T6.CO_EMPR          
 And T1.CO_MONE = T7.CO_MONE          
    
 And ( ( Len(Ltrim(RTRIM(@ISCO_PROV_REPO))) > 0 And T3.CO_PROV = @ISCO_PROV_REPO ) OR @ISCO_PROV_REPO IS Null )          
 AND T3.FE_EMIS >= @VDFE_EMIS          
 And T3.FE_EMIS <= @VDFE_FINA           
-------------------------------------          
 And T3.Fe_Regi_Comp <= @VDFE_FINA          
--------------------------------------          
          
    
/*Actualizo el Saldo Real(Total - Pagos) del Documento*/          
-- SOLES          
           
Update #TWREGI_RETE_Q01          
Set  #TWREGI_RETE_Q01.IM_DOCU_SOLE = #TWREGI_RETE_Q01.IM_DOCU_SOLE -           
     (Select  IsNull(Sum(Isnull(T2.IM_PAGO_SOLE,0)), 0)          
      From    #TWREGI_RETE_Q01 T2           
      Where   #TWREGI_RETE_Q01.CO_PROV = T2.CO_PROV          
       And (          
       (   #TWREGI_RETE_Q01.CO_SUNA_DOCU = T2.CO_SUNA_DOCU And           
           #TWREGI_RETE_Q01.NU_SERI_DOCU = T2.NU_SERI_DOCU And           
                #TWREGI_RETE_Q01.NU_CORR_DOCU = T2.NU_CORR_DOCU )          
       Or           
       ( #TWREGI_RETE_Q01.NU_CORR_PROV = T2.NU_CORR_PROV )          
            )          
       And T2.NU_ORDE != 1          
       And T2.FE_OPER < @VDFE_INIC)          
Where  #TWREGI_RETE_Q01.NU_ORDE = 1          
 And Exists(Select  T2.NU_ORDE           
     From    #TWREGI_RETE_Q01 T2           
     Where   #TWREGI_RETE_Q01.CO_PROV = T2.CO_PROV          
      And (          
      ( #TWREGI_RETE_Q01.CO_SUNA_DOCU = T2.CO_SUNA_DOCU And           
        #TWREGI_RETE_Q01.NU_SERI_DOCU = T2.NU_SERI_DOCU And           
        #TWREGI_RETE_Q01.NU_CORR_DOCU = T2.NU_CORR_DOCU )          
      Or          
      ( #TWREGI_RETE_Q01.NU_CORR_PROV = T2.NU_CORR_PROV )          
    )          
          
      And T2.NU_ORDE != 1)          
          
          
-- DOLARES          
Update #TWREGI_RETE_Q01          
Set  #TWREGI_RETE_Q01.IM_DOCU_DOLA = #TWREGI_RETE_Q01.IM_DOCU_DOLA -           
     (Select  IsNull( Sum(isnull(T2.IM_PAGO_DOLA,0)), 0)          
      From    #TWREGI_RETE_Q01 T2           
      Where   #TWREGI_RETE_Q01.CO_PROV = T2.CO_PROV          
       And (          
       ( #TWREGI_RETE_Q01.CO_SUNA_DOCU = T2.CO_SUNA_DOCU And           
         #TWREGI_RETE_Q01.NU_SERI_DOCU = T2.NU_SERI_DOCU And           
         #TWREGI_RETE_Q01.NU_CORR_DOCU = T2.NU_CORR_DOCU )          
       Or          
       ( #TWREGI_RETE_Q01.NU_CORR_PROV = T2.NU_CORR_PROV )           
           )          
       And T2.NU_ORDE != 1          
       And T2.FE_OPER < @VDFE_INIC)          
Where  #TWREGI_RETE_Q01.NU_ORDE = 1          
 And Exists(Select  T2.NU_ORDE           
     From    #TWREGI_RETE_Q01 T2           
     Where   #TWREGI_RETE_Q01.CO_PROV = T2.CO_PROV          
      And (          
      ( #TWREGI_RETE_Q01.CO_SUNA_DOCU = T2.CO_SUNA_DOCU And           
        #TWREGI_RETE_Q01.NU_SERI_DOCU = T2.NU_SERI_DOCU And           
        #TWREGI_RETE_Q01.NU_CORR_DOCU = T2.NU_CORR_DOCU )          
      Or          
      ( #TWREGI_RETE_Q01.NU_CORR_PROV = T2.NU_CORR_PROV )          
          )          
      And T2.NU_ORDE != 1)          
          
          
/*Borro los mvmtos. que existen como egreso de*/          
/*bancos y como comprobante de retencion*/          
          
Delete  #TWREGI_RETE_Q01          
Where   #TWREGI_RETE_Q01.CO_PROV = (Select   Distinct T2.CO_PROV          
         From     #TWREGI_RETE_Q02 T2          
         Where    #TWREGI_RETE_Q01.CO_PROV = T2.CO_PROV          
           And #TWREGI_RETE_Q01.FE_EMIS_DOCU = T2.FE_EMIS_DOCU          
           And #TWREGI_RETE_Q01.CO_MONE_DOCU = T2.CO_MONE_DOCU          
          
           And (          
          ( #TWREGI_RETE_Q01.NU_SERI_DOCU = T2.NU_SERI_DOCU And           
            #TWREGI_RETE_Q01.NU_CORR_DOCU = T2.NU_CORR_DOCU)          
          Or          
          (#TWREGI_RETE_Q01.NU_CORR_PROV = T2.NU_CORR_PROV)          
      )          
          
           And #TWREGI_RETE_Q01.CO_BACA_PAGO = T2.CO_BACA_PAGO)          
 And #TWREGI_RETE_Q01.NU_ORDE != 6          
          
          
/*UPDATE los mvmtos. RENDICIONES*/          
          
          
UPDATE  #TWREGI_RETE_Q01          
Set  #TWREGI_RETE_Q01.IM_DOCU_DOLA = 0,           
 #TWREGI_RETE_Q01.IM_DOCU_SOLE = 0          
Where   #TWREGI_RETE_Q01.NU_ORDE = 1          
 And EXISTS (Select  t3.NU_CORR_PROV           
        From  #TWREGI_RETE_Q01 t3          
        Where  t3.NU_CORR_PROV = #TWREGI_RETE_Q01.NU_CORR_PROV          
    and t3.nu_orde IN(4,6)          
    and t3.TI_OPER IN('Rend x Bancos', 'Dif Camb Rend', 'Rend x Caja')           
    AND (          
     (Year(T3.FE_EMIS_DOCU)= @ISNU_ANNO AND Month(T3.FE_EMIS_DOCU) < @ISNU_MESE )          
     OR          
     --(Year(T3.FE_EMIS_DOCU)!= @ISNU_ANNO AND Month(T3.FE_EMIS_DOCU) != @ISNU_MESE )          
     (Year(T3.FE_EMIS_DOCU)!= @ISNU_ANNO)          
             )          
      )          
          
/* ----------------- Si s½lo se visualizan documentos que tienen retenci½n ----------------- */    
    
If @ISST_RETE = 'S'      
  BEGIN      
    
 -- Se eliminan registros de ajustes de otros periodos    
 DELETE #TWREGI_RETE_Q01    
 WHERE (YEAR(FE_EMIS_DOCU) != CONVERT(INT, @ISNU_ANNO)    
 OR ( YEAR(FE_EMIS_DOCU) = CONVERT(INT, @ISNU_ANNO)    
 AND MONTH(FE_EMIS_DOCU) != CONVERT(INT, @ISNU_MESE)))    
 AND NU_ORDE = 6    
    
    
 -- Se eliminan registros de retenciones de otros periodos    
 DELETE #TWREGI_RETE_Q02    
 WHERE (YEAR(FE_EMIS_DOCU) != CONVERT(INT, @ISNU_ANNO)    
 OR ( YEAR(FE_EMIS_DOCU) = CONVERT(INT, @ISNU_ANNO)    
 AND MONTH(FE_EMIS_DOCU) != CONVERT(INT, @ISNU_MESE)))    
     
     
 -- Se eliminan registros que no tengan retenciones en el periodo    
 DELETE #TWREGI_RETE_Q01      
 FROM #TWREGI_RETE_Q01 T1      
 WHERE NOT EXISTS (Select  T5.CO_EMPR           
     From TCCOMP_RETE T5, TDCOMP_RETE T6          
     Where T5.CO_EMPR = @ISCO_EMPR    
     And T5.CO_EMPR = T6.CO_EMPR    
     And T5.NU_COMP_RETE = T6.NU_COMP_RETE    
     And T5.TI_SITU = 'ACT'    
     And YEAR(T5.FE_COMP_RETE) = CONVERT(INT, @ISNU_ANNO)          
     And MONTH(T5.FE_COMP_RETE) = CONVERT(INT, @ISNU_MESE)          
     And T1.CO_PROV = T5.CO_PROV          
     --And T1.CO_TIPO_DOCU = T6.TI_DOCU           
     And T1.NU_SERI_DOCU + '-' + T1.NU_CORR_DOCU = T6.NU_DOCU)      
  
  
 UPDATE #TWREGI_RETE_Q01      
 SET NU_SERI_RETE = T2.NU_SERI_RETE,      
 NU_CORR_RETE = T2.NU_CORR_RETE      
 FROM #TWREGI_RETE_Q01 T1, (SELECT CO_PROV, NU_SERI_DOCU, NU_CORR_DOCU, NU_SERI_RETE, NU_CORR_RETE    
     FROM #TWREGI_RETE_Q02    
     WHERE NU_CORR_RETE IS NOT NULL    
  AND CO_SUNA_DOCU != 'ANULADO') T2      
 WHERE T1.CO_PROV = T2.CO_PROV    
 AND T1.NU_SERI_DOCU = T2.NU_SERI_DOCU    
 AND T1.NU_CORR_DOCU = T2.NU_CORR_DOCU    
 END          
          
/* SELECT PARA EL REPORTE (QRP)*/          
          
--SELECT * FROM #TWREGI_RETE_Q01    
--SELECT * FROM #TWREGI_RETE_Q02           
    
          
If @ISST_ORDE = 'P'          
 Select   t1.CO_PROV, t1.NO_PROV,           
   CASE WHEN t1.NU_ORDE = 1 THEN t1.FE_PARA_REPO ELSE t1.FE_EMIS_DOCU END,           
   t1.CO_MONE_DOCU, t1.FA_TICA_DOCU,           
   t1.IM_DOCU_SOLE, t1.IM_DOCU_DOLA, t1.NU_SERI_DOCU, t1.NU_CORR_DOCU,           
   t1.NU_CORR_PROV, t1.FE_PAGO_BACA, t1.CO_SUNA_DOCU, t1.CO_BACA_PAGO,           
   t1.CO_MONE_PAGO, t1.NU_SERI_RETE, t1.NU_CORR_RETE, t1.FA_TICA_PAGO,           
   t1.IM_PAGO_SOLE, t1.IM_PAGO_DOLA, t1.IM_RETE_SOLE, t1.IM_RETE_DOLA,           
   t1.IM_TORE_SOLE, t1.IM_TORE_DOLA, t1.TI_OPER,           
   Case When (           
    (t1.CO_MONE_DOCU ='SOL' And t1.CO_MONE_PAGO = 'SOL' And t1.NU_ORDE = 6)          
    OR (t1.NU_ORDE = 2.5 AND t1.IM_DIFE_DEBE = 0 AND t1.IM_DIFE_HABER = 0)          
      )           
   Then 0           
   Else 1 End, t1.IM_DIFE_DEBE, t1.IM_DIFE_HABER, t1.NU_ORDE, t1.FA_TICA_OPER,           
   t1.NU_CHEQ, t1.NU_DOCU_REFE--, 1          
 From  #TWREGI_RETE_Q01 t1           
 Where  ( t1.NU_ORDE = 1 OR t1.NU_ORDE = 3 )          
   And Not Exists (Select t2.NU_CORR_PROV           
     From  #TWREGI_RETE_Q01 t2          
     Where  t2.NU_ORDE != 1 And t2.NU_ORDE != 3        
     And t2.NU_CORR_PROV = t1.NU_CORR_PROV )          
           
 UNION          
           
 -- Este Select se usa para los dcmtos con saldo          
           
 Select   t1.CO_PROV, t1.NO_PROV,           
   CASE WHEN t1.NU_ORDE = 1 THEN t1.FE_PARA_REPO ELSE t1.FE_EMIS_DOCU END,           
   t1.CO_MONE_DOCU, t1.FA_TICA_DOCU,           
   t1.IM_DOCU_SOLE, t1.IM_DOCU_DOLA, t1.NU_SERI_DOCU, t1.NU_CORR_DOCU,           
   t1.NU_CORR_PROV, t1.FE_PAGO_BACA, t1.CO_SUNA_DOCU, t1.CO_BACA_PAGO,           
   t1.CO_MONE_PAGO, t1.NU_SERI_RETE, t1.NU_CORR_RETE, t1.FA_TICA_PAGO,           
   t1.IM_PAGO_SOLE, t1.IM_PAGO_DOLA, t1.IM_RETE_SOLE, t1.IM_RETE_DOLA,           
   t1.IM_TORE_SOLE, t1.IM_TORE_DOLA, t1.TI_OPER,           
   Case When (           
    (t1.CO_MONE_DOCU ='SOL' And t1.CO_MONE_PAGO = 'SOL' And t1.NU_ORDE = 6)          
    OR (t1.NU_ORDE = 2.5 AND t1.IM_DIFE_DEBE = 0 AND t1.IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, t1.IM_DIFE_DEBE, t1.IM_DIFE_HABER, t1.NU_ORDE, t1.FA_TICA_OPER,           
   t1.NU_CHEQ, t1.NU_DOCU_REFE--, 2          
 From  #TWREGI_RETE_Q01 t1           
 Where  T1.NU_ORDE = 1          
   And (T1.IM_DOCU_DOLA != 0 Or T1.IM_DOCU_SOLE != 0)          
           
 UNION          
 /*          
 Select   CO_PROV, NO_PROV,           
   CASE WHEN NU_ORDE = 1 THEN FE_PARA_REPO ELSE FE_EMIS_DOCU END,           
   CO_MONE_DOCU, FA_TICA_DOCU,           
   IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
   CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
   IM_PAGO_SOLE, IM_PAGO_DOLA, IM_RETE_SOLE, IM_RETE_DOLA,           
   IM_TORE_SOLE, IM_TORE_DOLA, TI_OPER,           
   Case When (           
    (CO_MONE_DOCU ='SOL' And CO_MONE_PAGO = 'SOL' And NU_ORDE = 6)          
    OR (NU_ORDE = 2.5 AND IM_DIFE_DEBE = 0 AND IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, IM_DIFE_DEBE, IM_DIFE_HABER, NU_ORDE, FA_TICA_OPER,           
   NU_CHEQ, NU_DOCU_REFE, 3          
 From  #TWREGI_RETE_Q01          
 Where  ( NU_ORDE = 1 OR NU_ORDE = 3 )          
   AND (          
   ( Year(FE_EMIS_DOCU) = @ISNU_ANNO And Month(FE_EMIS_DOCU) = @ISNU_MESE )          
   OR (          
    ( Year(FE_EMIS_DOCU) != @ISNU_ANNO OR Month(FE_EMIS_DOCU) != @ISNU_MESE ) AND           
           
    (           
        Exists( Select  T2.CO_PROV           
    From    #TWREGI_RETE_Q01 T2           
         Where   #TWREGI_RETE_Q01.CO_PROV = T2.CO_PROV          
          And (           
          ( #TWREGI_RETE_Q01.NU_SERI_DOCU = T2.NU_SERI_DOCU And           
            #TWREGI_RETE_Q01.NU_CORR_DOCU = T2.NU_CORR_DOCU )          
           Or          
          ( #TWREGI_RETE_Q01.NU_CORR_PROV = T2.NU_CORR_PROV )           
        )          
          And T2.NU_ORDE != 1       
          And Year(FE_OPER) = @ISNU_ANNO AND Month(FE_OPER) = @ISNU_MESE )           
    )            
    --AND ( IM_DOCU_SOLE > 0 OR IM_DOCU_DOLA > 0 )          
    AND (           
     (CO_SUNA_DOCU != '07' AND IM_DOCU_SOLE > 0 OR IM_DOCU_DOLA > 0 )       
     OR          
     (CO_SUNA_DOCU = '07' AND IM_PAGO_SOLE > 0 OR IM_PAGO_DOLA > 0 )           
        )          
      )          
       )          
           
 UNION          
 */      
 Select   CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
   IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
   CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
   IM_PAGO_SOLE, IM_PAGO_DOLA, IM_RETE_SOLE, IM_RETE_DOLA,           
   IM_TORE_SOLE, IM_TORE_DOLA, TI_OPER,           
   Case When (           
    (CO_MONE_DOCU = 'SOL' And CO_MONE_PAGO = 'SOL' And NU_ORDE = 6)          
    OR (NU_ORDE = 2.5 AND IM_DIFE_DEBE = 0 AND IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, IM_DIFE_DEBE, IM_DIFE_HABER, NU_ORDE, FA_TICA_OPER,           
   NU_CHEQ, NU_DOCU_REFE--, 4          
 From  #TWREGI_RETE_Q01          
 Where  ( NU_ORDE != 1 AND NU_ORDE != 3 )          
           
   AND (          
   ( Year(FE_OPER) = @ISNU_ANNO And Month(FE_OPER) = @ISNU_MESE )          
   OR (          
    ( Year(FE_OPER) >= @ISNU_ANNO AND Month(FE_OPER) = @ISNU_MESE ) AND           
    ( IM_PAGO_SOLE > 0 OR IM_PAGO_DOLA > 0 )          
      )          
       )          
           
   AND NOT EXISTS (SELECT CO_PROV FROM #TWREGI_RETE_Q02          
   WHERE  #TWREGI_RETE_Q01.CO_PROV = #TWREGI_RETE_Q02.CO_PROV           
    AND   #TWREGI_RETE_Q01.NU_CORR_DOCU = #TWREGI_RETE_Q02.NU_CORR_DOCU           
    AND   #TWREGI_RETE_Q01.CO_BACA_PAGO = #TWREGI_RETE_Q02.CO_BACA_PAGO           
    AND   #TWREGI_RETE_Q01.IM_PAGO_SOLE = #TWREGI_RETE_Q02.IM_PAGO_SOLE          
    AND   #TWREGI_RETE_Q01.IM_PAGO_DOLA = #TWREGI_RETE_Q02.IM_PAGO_DOLA)          
           
           
 UNION          
           
 Select   CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
   IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
   CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
   IM_PAGO_SOLE, IM_PAGO_DOLA, IM_RETE_SOLE, IM_RETE_DOLA,           
   IM_TORE_SOLE, IM_TORE_DOLA, TI_OPER,           
   Case When (           
    (CO_MONE_DOCU ='SOL' And CO_MONE_PAGO = 'SOL' And NU_ORDE = 6)          
    OR (NU_ORDE = 2.5 AND IM_DIFE_DEBE = 0 AND IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, IM_DIFE_DEBE, IM_DIFE_HABER, NU_ORDE, FA_TICA_OPER,           
   NU_CHEQ, NU_DOCU_REFE--, 5          
 From  #TWREGI_RETE_Q02          
 Where  ( Year(FE_OPER) = @ISNU_ANNO And Month(FE_OPER) = @ISNU_MESE )          
 Order By CO_PROV, NU_ORDE          
       
ELSE      
      
 Select   t1.CO_PROV, t1.NO_PROV,           
   CASE WHEN t1.NU_ORDE = 1 THEN t1.FE_PARA_REPO ELSE t1.FE_EMIS_DOCU END,           
   t1.CO_MONE_DOCU, t1.FA_TICA_DOCU,           
   t1.IM_DOCU_SOLE, t1.IM_DOCU_DOLA, t1.NU_SERI_DOCU, t1.NU_CORR_DOCU,           
   t1.NU_CORR_PROV, t1.FE_PAGO_BACA, t1.CO_SUNA_DOCU, t1.CO_BACA_PAGO,           
   t1.CO_MONE_PAGO, t1.NU_SERI_RETE, t1.NU_CORR_RETE, t1.FA_TICA_PAGO,           
   t1.IM_PAGO_SOLE, t1.IM_PAGO_DOLA, t1.IM_RETE_SOLE, t1.IM_RETE_DOLA,           
   t1.IM_TORE_SOLE, t1.IM_TORE_DOLA, t1.TI_OPER,           
   Case When (           
    (t1.CO_MONE_DOCU ='SOL' And t1.CO_MONE_PAGO = 'SOL' And t1.NU_ORDE = 6)          
    OR (t1.NU_ORDE = 2.5 AND t1.IM_DIFE_DEBE = 0 AND t1.IM_DIFE_HABER = 0)          
      )           
   Then 0           
   Else 1 End, t1.IM_DIFE_DEBE, t1.IM_DIFE_HABER, t1.NU_ORDE, t1.FA_TICA_OPER,           
   t1.NU_CHEQ, t1.NU_DOCU_REFE--, 1          
 From  #TWREGI_RETE_Q01 t1           
 Where  ( t1.NU_ORDE = 1 OR t1.NU_ORDE = 3 )          
   And Not Exists (Select t2.NU_CORR_PROV           
     From  #TWREGI_RETE_Q01 t2          
     Where  t2.NU_ORDE != 1 And t2.NU_ORDE != 3        
     And t2.NU_CORR_PROV = t1.NU_CORR_PROV )          
           
 UNION          
           
 -- Este Select se usa para los dcmtos con saldo          
           
 Select   t1.CO_PROV, t1.NO_PROV,           
   CASE WHEN t1.NU_ORDE = 1 THEN t1.FE_PARA_REPO ELSE t1.FE_EMIS_DOCU END,           
   t1.CO_MONE_DOCU, t1.FA_TICA_DOCU,           
   t1.IM_DOCU_SOLE, t1.IM_DOCU_DOLA, t1.NU_SERI_DOCU, t1.NU_CORR_DOCU,           
   t1.NU_CORR_PROV, t1.FE_PAGO_BACA, t1.CO_SUNA_DOCU, t1.CO_BACA_PAGO,           
   t1.CO_MONE_PAGO, t1.NU_SERI_RETE, t1.NU_CORR_RETE, t1.FA_TICA_PAGO,           
   t1.IM_PAGO_SOLE, t1.IM_PAGO_DOLA, t1.IM_RETE_SOLE, t1.IM_RETE_DOLA,           
   t1.IM_TORE_SOLE, t1.IM_TORE_DOLA, t1.TI_OPER,           
   Case When (           
    (t1.CO_MONE_DOCU ='SOL' And t1.CO_MONE_PAGO = 'SOL' And t1.NU_ORDE = 6)          
    OR (t1.NU_ORDE = 2.5 AND t1.IM_DIFE_DEBE = 0 AND t1.IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, t1.IM_DIFE_DEBE, t1.IM_DIFE_HABER, t1.NU_ORDE, t1.FA_TICA_OPER,           
   t1.NU_CHEQ, t1.NU_DOCU_REFE--, 2          
 From  #TWREGI_RETE_Q01 t1           
 Where  T1.NU_ORDE = 1          
   And (T1.IM_DOCU_DOLA != 0 Or T1.IM_DOCU_SOLE != 0)          
           
 UNION          
 /*          
 Select   CO_PROV, NO_PROV,           
   CASE WHEN NU_ORDE = 1 THEN FE_PARA_REPO ELSE FE_EMIS_DOCU END,           
   CO_MONE_DOCU, FA_TICA_DOCU,           
   IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
   CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
   IM_PAGO_SOLE, IM_PAGO_DOLA, IM_RETE_SOLE, IM_RETE_DOLA,           
   IM_TORE_SOLE, IM_TORE_DOLA, TI_OPER,           
   Case When (           
    (CO_MONE_DOCU ='SOL' And CO_MONE_PAGO = 'SOL' And NU_ORDE = 6)          
    OR (NU_ORDE = 2.5 AND IM_DIFE_DEBE = 0 AND IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, IM_DIFE_DEBE, IM_DIFE_HABER, NU_ORDE, FA_TICA_OPER,           
   NU_CHEQ, NU_DOCU_REFE, 3          
 From  #TWREGI_RETE_Q01          
 Where  ( NU_ORDE = 1 OR NU_ORDE = 3 )          
   AND (          
   ( Year(FE_EMIS_DOCU) = @ISNU_ANNO And Month(FE_EMIS_DOCU) = @ISNU_MESE )          
   OR (          
    ( Year(FE_EMIS_DOCU) != @ISNU_ANNO OR Month(FE_EMIS_DOCU) != @ISNU_MESE ) AND           
           
    (           
        Exists( Select  T2.CO_PROV           
         From    #TWREGI_RETE_Q01 T2           
         Where   #TWREGI_RETE_Q01.CO_PROV = T2.CO_PROV          
          And (           
          ( #TWREGI_RETE_Q01.NU_SERI_DOCU = T2.NU_SERI_DOCU And           
            #TWREGI_RETE_Q01.NU_CORR_DOCU = T2.NU_CORR_DOCU )          
           Or          
          ( #TWREGI_RETE_Q01.NU_CORR_PROV = T2.NU_CORR_PROV )           
        )          
          And T2.NU_ORDE != 1       
          And Year(FE_OPER) = @ISNU_ANNO AND Month(FE_OPER) = @ISNU_MESE )           
    )            
    --AND ( IM_DOCU_SOLE > 0 OR IM_DOCU_DOLA > 0 )          
    AND (           
     (CO_SUNA_DOCU != '07' AND IM_DOCU_SOLE > 0 OR IM_DOCU_DOLA > 0 )          
     OR          
     (CO_SUNA_DOCU = '07' AND IM_PAGO_SOLE > 0 OR IM_PAGO_DOLA > 0 )           
        )          
      )          
       )          
           
 UNION          
 */      
 Select   CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
   IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
   CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
   IM_PAGO_SOLE, IM_PAGO_DOLA, IM_RETE_SOLE, IM_RETE_DOLA,           
   IM_TORE_SOLE, IM_TORE_DOLA, TI_OPER,           
   Case When (           
    (CO_MONE_DOCU = 'SOL' And CO_MONE_PAGO = 'SOL' And NU_ORDE = 6)          
    OR (NU_ORDE = 2.5 AND IM_DIFE_DEBE = 0 AND IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, IM_DIFE_DEBE, IM_DIFE_HABER, NU_ORDE, FA_TICA_OPER,           
   NU_CHEQ, NU_DOCU_REFE--, 4          
 From  #TWREGI_RETE_Q01          
 Where  ( NU_ORDE != 1 AND NU_ORDE != 3 )          
           
   AND (          
   ( Year(FE_OPER) = @ISNU_ANNO And Month(FE_OPER) = @ISNU_MESE )          
   OR (          
    ( Year(FE_OPER) >= @ISNU_ANNO AND Month(FE_OPER) = @ISNU_MESE ) AND           
    ( IM_PAGO_SOLE > 0 OR IM_PAGO_DOLA > 0 )          
      )          
       )          
           
   AND NOT EXISTS (SELECT CO_PROV FROM #TWREGI_RETE_Q02          
   WHERE  #TWREGI_RETE_Q01.CO_PROV = #TWREGI_RETE_Q02.CO_PROV           
    AND   #TWREGI_RETE_Q01.NU_CORR_DOCU = #TWREGI_RETE_Q02.NU_CORR_DOCU           
    AND   #TWREGI_RETE_Q01.CO_BACA_PAGO = #TWREGI_RETE_Q02.CO_BACA_PAGO           
    AND   #TWREGI_RETE_Q01.IM_PAGO_SOLE = #TWREGI_RETE_Q02.IM_PAGO_SOLE          
    AND   #TWREGI_RETE_Q01.IM_PAGO_DOLA = #TWREGI_RETE_Q02.IM_PAGO_DOLA)          
           
           
 UNION          
           
 Select   CO_PROV, NO_PROV, FE_EMIS_DOCU, CO_MONE_DOCU, FA_TICA_DOCU,           
   IM_DOCU_SOLE, IM_DOCU_DOLA, NU_SERI_DOCU, NU_CORR_DOCU,           
   NU_CORR_PROV, FE_PAGO_BACA, CO_SUNA_DOCU, CO_BACA_PAGO,           
   CO_MONE_PAGO, NU_SERI_RETE, NU_CORR_RETE, FA_TICA_PAGO,           
   IM_PAGO_SOLE, IM_PAGO_DOLA, IM_RETE_SOLE, IM_RETE_DOLA,           
   IM_TORE_SOLE, IM_TORE_DOLA, TI_OPER,           
   Case When (     
    (CO_MONE_DOCU ='SOL' And CO_MONE_PAGO = 'SOL' And NU_ORDE = 6)          
    OR (NU_ORDE = 2.5 AND IM_DIFE_DEBE = 0 AND IM_DIFE_HABER = 0)          
      )          
   Then 0           
   Else 1 End, IM_DIFE_DEBE, IM_DIFE_HABER, NU_ORDE, FA_TICA_OPER,           
   NU_CHEQ, NU_DOCU_REFE--, 5          
 From  #TWREGI_RETE_Q02          
 Where  ( Year(FE_OPER) = @ISNU_ANNO And Month(FE_OPER) = @ISNU_MESE )          
 Order By NU_SERI_RETE, NU_CORR_RETE, CO_PROV, NU_ORDE          
       
      
     
RETURN          
/* -------------------------------- Fin -------------------------------- */


GO
